Create the query as a string and execute that in PostgreSQL 10

Question

I am trying to create the query as a string and execute that in PostgreSQL 10.

As far as I know, I can use the EXECUTE command to execute my query from a defined string.

Unfortunately, I have got an error: SQL Error [42601]: ERROR: syntax error at or near "execute"

Below is my code:

drop table if exists delinquent;
create table  delinquent 
(
    report_date date
    ,account_id text
)
;
INSERT INTO delinquent VALUES('2019-07-23', 'a1234');
INSERT INTO delinquent VALUES('2019-07-23', 'b5679');
--------------
drop table if exists output1;
create temp table  output1 
(
    report_date date
    ,account_id text
)
;
--------------
do $$
    declare table_name text := 'delinquent';
begin
    truncate table output1;
    insert into output1
    execute concat('select * from ',table_name);
end; $$;
select * from output1;

Anybody has an idea on what is wrong and what to do about it?

Many thanks,

score 1 · Accepted Answer · 2019-07-23 16:26:55Z

1

You need to run the complete INSERT statement as dynamic SQL. And to build dynamic SQL, using format() is highly recommended to properly deal with identifiers and literals:

do $$
declare 
  table_name text := 'delinquent';
  some_value text := 'a1234';
begin
  truncate table output1;
  execute format('insert into output1 select * from %I where some_column = %L',
                  table_name, some_value);
end; $$;

select * 
from output1;

edited Jul 23, 2019 at 16:26

answered Jul 23, 2019 at 16:06

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Rosy Over a year ago

Thanks for @a_horse_with_no_name your quick response. But if I have multiple variables, how the code is? execute concat('select * from ',table_name, ' where column_name = ','a1234',''''); select * from delinquent where account_id = 'a1234'; Kindly please help me with this? Many thanks,

user330315 Over a year ago

@Rosy: as documented in the manual use %L for a literal

Rosy Over a year ago

Thank you very much @a_horse_with_no_name

Collectives™ on Stack Overflow

Create the query as a string and execute that in PostgreSQL 10

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related