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 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:

    RETURNS void AS $$

    $$ LANGUAGE plpgsql

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 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!
Theresa ReidSQL RookieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
NerdsOfTechTechnology ScientistCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Theresa ReidSQL RookieAuthor 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:

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

NerdsOfTechTechnology ScientistCommented:
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 ScientistCommented:
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 RookieAuthor 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 ScientistCommented:
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).
earth man2Commented:
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 RookieAuthor 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?
earth man2Commented:
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 ScientistCommented:
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 ScientistCommented:
UDF can be added via the database itself or via a tool that supports UDF
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.