CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1886

How to insert rows to table in a loop

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

Answers (2)

user330315
user330315

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

Bulat
Bulat

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

Related Questions