Reputation: 1886
I have the following plpgsql
function in PostgreSQL
:
CREATE OR REPLACE FUNCTION func1()
RETURNS SETOF type_a AS
$BODY$
declare
param text;
sqls varchar;
row type_a;
begin
code.....
sqls='select * from func3(' || param || ') ';
for row in execute sqls LOOP
return next row;
END LOOP;
end if;
return;
end
$BODY$
LANGUAGE plpgsql VOLATILE
I want to add an insert
statment into the loop, so that the loop will work as it is now but also all rows will be saved in a table.
for row in execute sqls LOOP
INSERT INTO TABLE new_tab(id, name)
return next row;
the thing is that I don't know how to do that... the insert statment normaly has syntax of:
INSERT INTO new_tab(id, name)
SELECT x.id, x.name
FROM y
but this syntax doesn't fit here. There is no query to select rows from.... the rows are in the loop.
Upvotes: 1
Views: 313
Reputation:
No need for a loop, you can use insert .. select ... returning
in dynamic SQL just as well:
create or replace function func1()
returns table (id integer, name text)
as
$$
declare
param text;
begin
param := ... ;
return query execute
'insert into new_tab (id, name)
select id, name
from func3($1)
returning *'
using param;
end;
$$
language plpgsql;
Note that I used a parameter placeholder and the USING
clause instead of concatenating the parameter into the query - much more robust.
Upvotes: 1
Reputation: 6979
Basic insert with values looks like this:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Based on the additional comments you need to use cursor instead of execute sqls
.
Upvotes: 1