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