We help IT Professionals succeed at work.
Get Started

Create stored procedure or function which updates a table.

567 Views
Last Modified: 2018-08-25
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
Technology Scientist
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 3 Answers and 12 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE