How to insert lines in a loop

advertisements

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.