How to insert data from stored procedure into a new table using postgresql

Question

I have created a stored procedure using PGADMIN4. Inside the SP, I have selected a view table. However, I want the data inside the stored procedure to be inserted into a new table. I have tried below code, but it is showing an error:

SP name: Test

New table name: Customer

Insert into public.Customer exec public.Test

This is my SP code:

create procedure test()
language plpgsql
as $$
BEGIN
Select * from public.customer_list; 

END; 
$$;

ERROR: syntax error at or near "exec"

1) You will need to add the code of your stored procedure to your question 2) If you are actually using a SP it does not have RETURN , see here SP return 3) A SP needs to be used with CALL — Adrian Klaver
– Adrian Klaver, Commented Oct 16, 2021 at 17:34
Convert your procedure to a function that returns a table. Procedures aren't meant to return result sets — user330315
– user330315, Commented Oct 16, 2021 at 17:35
You will need a function not a procedure. For this case the simplest way is RETURN QUERY, see here Returning 43.6.1.2. RETURN NEXT and RETURN QUERY and look at examples. Then something like Insert into public.Customer select * from public.test();. — Adrian Klaver
– Adrian Klaver, Commented Oct 16, 2021 at 17:45

Belayer · Accepted Answer · 2021-10-16 19:07:50Z

1

Procedures cannot be used in SQL. If you must have a stored procedure (define as code stored in the database to be executed) then change to a SQL function. Which returns a type.

create function copy_customer_list()
  returns setof customer_list 
  language sql
as $$
   Select * from customer_list; 
$$;

Then you can insert into other table with

insert into customer  
  select * from copy_customer_list();

answered Oct 16, 2021 at 19:07

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert data from stored procedure into a new table using postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related