CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
user3797729
user3797729

Reputation: 87

How to execute insert statement dynamically

Our database is PostgreSQL 9.3.5. We need to execute insert statement dynamically. For that we have written code like this:

v_seq bigint;
v_levelname varchar(100);
v_cols  text;
v_cols variable having  concatenated data of all column names of particular a table(ex. L150).

L150 table columns details
---------------------------- 
id  bigint;
levelname varchar(100);
healthcareno bigint;
businessno bigint;
bankingno bigint;

v_insertstmt:='insert into L'||levelid||'---tablename(receiving tablename dynamically)
values('||v_seq||','''||v_levelname||''','||v_cols||')';

raise notice '%',v_insertstmt;

Data output:

insert into L105
values(1053911,''ProductPlanning'','||healthcareno||','||businessno||','||bankingno||')

but healthcareno,businessno,bankingno these are columns.Each column has values we need to insert those values into the table.

v_str:=''''||v_insertstmt||'''';--we added quotes

raise notice '%',v_str;

Data output:

'insert into L105
values(1053911,''ProductPlanning'','||healthcareno||','||businessno||','||bankingno||')'

execute v_str;

But we were getting syntax error.

Upvotes: 1

Views: 11520

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

Output ''ProductPlanning'' is wrong. Use a USING clause instead:

execute format('insert into %I values($1, $2, $3, $4, $5)', 'L' || levelid)
   using v_seq, v_levelname, ... ;

postgres=# create table foo(a int, b text);
CREATE TABLE
postgres=# do $$
postgres$# begin
postgres$# execute format('insert into %I values($1,$2)', 'foo') using 1, 'AHOJ';
postgres$# end;
postgres$# $$;
DO
postgres=# select * from foo;
 a |  b   
---+------
 1 | AHOJ
(1 row)

Upvotes: 4

Related Questions