Create stored procedure or function which updates a table.

Theresa Reid
Theresa Reid used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technology Scientist
Commented:
You should be able to make the UDF (User-defined function) in PostgreSQL directly

Or, you could use a different web-based administration tool such as phpPgAdmin (which supports UDF) to add it to your database.
Theresa ReidSQL Rookie

Author

Commented:
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
NerdsOfTechTechnology Scientist
Commented:
Yes, the limitation allows only a 'scalar' UDF which isn't very useful. It would be best if the fully functional UDF is created in the database via PostgreSQL or another tool like phpPgAdmin.

You could use a server-side script do this as well (PHP, etc.)
NerdsOfTechTechnology Scientist

Commented:
What does your proposed function do? Maybe there is a predefined function that exists in Redshift... Or maybe a VIEW is in order?
Theresa ReidSQL Rookie

Author

Commented:
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.
NerdsOfTechTechnology Scientist

Commented:
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).
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.
Theresa ReidSQL Rookie

Author

Commented:
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.
NerdsOfTechTechnology Scientist
Commented:
The hard code option still remains: add the UDF into PostgreSQL (via console or another tool) and call it from the app.

It's unfortunate that this simple functionally is not available in RedShift.
NerdsOfTechTechnology Scientist

Commented:
UDF can be added via the database itself or via a tool that supports UDF

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial