Theresa Reid
asked on
Create stored procedure or function which updates a table.
I'm trying to do something super simple...or seems it should be. I have a script that truncates and updates a table...it creates unique combinations across three other tables for a dimension build. I was hoping to create a function/procedure that I could call from an application in a single line, rather than trying to store the sql in my application.
We are using postgresql and sqlworkbench/j. My code to create the table includes a view creation, distinct, union, and in insert into using select.
I was trying:
CREATE OR REPLACE FUNCTION update_table()
RETURNS void AS $$
BEGIN
CREATE OR REPLACE VIEW ...
...;
END;
$$ LANGUAGE plpgsql
IMMUTABLE;
I get the error that "Create Function is not supported".
I'm admittedly a sql rookie and everything I have tried has failed, giving me one error after another. Is there a simpler way to do this? Nothing changes in my create statement...no parameters passed in, nada. It's simply that the underlying transactional data is changing and we're only keeping valid combos, so we trunc and reload the table.
Thanks in advance!
We are using postgresql and sqlworkbench/j. My code to create the table includes a view creation, distinct, union, and in insert into using select.
I was trying:
CREATE OR REPLACE FUNCTION update_table()
RETURNS void AS $$
BEGIN
CREATE OR REPLACE VIEW ...
...;
END;
$$ LANGUAGE plpgsql
IMMUTABLE;
I get the error that "Create Function is not supported".
I'm admittedly a sql rookie and everything I have tried has failed, giving me one error after another. Is there a simpler way to do this? Nothing changes in my create statement...no parameters passed in, nada. It's simply that the underlying transactional data is changing and we're only keeping valid combos, so we trunc and reload the table.
Thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got to this documentation as well, the issue is the below limitation...which eliminates my ability to run any of the scripts I am trying to put into procedures. Does that mean I'm up that one creek without a paddle?
The SELECT clause can't include any of the following types of clauses:
FROM
INTO
WHERE
GROUP BY
ORDER BY
LIMIT
The SELECT clause can't include any of the following types of clauses:
FROM
INTO
WHERE
GROUP BY
ORDER BY
LIMIT
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does your proposed function do? Maybe there is a predefined function that exists in Redshift... Or maybe a VIEW is in order?
ASKER
The function creates a distinct set of elements for use as a dimension data source. The problem is, when we had it created as a view, the subsequent access of that view by the application was VERY slow. It's essentially a union of distinct members across three very large transactional tables.
Using a combo of an interim view and an INSERT INTO( SELECT...) we were able to get the update speed to be around 20s and a resulting static table that could be access by our application more quickly.
Generally we've found that trying to use views as data sources has been super slow.
Using a combo of an interim view and an INSERT INTO( SELECT...) we were able to get the update speed to be around 20s and a resulting static table that could be access by our application more quickly.
Generally we've found that trying to use views as data sources has been super slow.
Are you able to create your UDF via:
another PostgreSQL tool; or
console/PostgreSQL (change the delimiter to slash, add UDF, change delimiter back to a semi-colon)?
Also, please provide the full function here as we might be able to optimize the XP (execution plan).
another PostgreSQL tool; or
console/PostgreSQL (change the delimiter to slash, add UDF, change delimiter back to a semi-colon)?
Also, please provide the full function here as we might be able to optimize the XP (execution plan).
Views are not necessarily slow, you need to create the right indexes and make sure that they are used. Another way to go is to use materialized views.
ASKER
Thanks guys. I'll try a couple of these options and get back to you. Unfortunately, because of time constraints, I may just put the SQL in the app itself (what I was trying to avoid in the first place) and come back to optimization later. Esp with that views comments. I thought RedShift didn't utilize indexes?
I've only just realised that you are probably using amazon redshift. This is a very different beast from postgresql, so plpgsql and indexes are not part of the mix. Just goes to show the disadvantages of using this product.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UDF can be added via the database itself or via a tool that supports UDF
http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html
You can create a custom user-defined 'scalar' function though
http://docs.aws.amazon.com/redshift/latest/dg/udf-security-and-privileges.html
http://docs.aws.amazon.com/redshift/latest/dg/udf-creating-a-scalar-sql-udf.html