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

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"

CodePudding user response：

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();