How to Run Multiple Dynamic Queries in a PostgreSQL Function

Question

I am having some issues figuring out how to run multiple dynamic queries in a single function.

 CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
BEGIN
 -- logic
    TRUNCATE TABLE 'schema_' || fyq || '.my_table'
    DROP TABLE 'schema_' || fyq || '.my_table';
END;
$BODY$;

I am generally getting syntax errors, like ERROR: syntax error at or near .... What am I doing wrong here?

Apart from not actually using dynamic SQL, your first dynamic query is also missing the semicolon — Bergi, May 20 '22 at 18:14

flwd · Accepted Answer · 2022-05-20T17:17:30.767

1

You can't simply concatenate strings to make a dynamic sql statement. Take a look at EXECUTE and EXECUTE IMMEDIATE.

In your case, you could use it like this:

CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
BEGIN
    -- logic
    EXECUTE 'TRUNCATE TABLE schema_' || fyq || '.my_table';
    EXECUTE 'DROP TABLE schema_' || fyq || '.my_table';
END
$BODY$;

edited May 20 '22 at 17:17

answered May 20 '22 at 17:06

flwd

548
3
12

Both of these worked well but I am giving your's the check as it followed my original method. – gwydion93 May 23 '22 at 12:59

emrdev · Answer 2 · 2022-11-23T10:13:49.107

1

Use the format function which will return a string and execute in your function.

create function permanently_delete_table(fyq text) RETURNS void
    LANGUAGE plpgsql AS $$
    declare
    begin
         EXECUTE format('TRUNCATE TABLE schema_%s.my_table',fyq); 
         EXECUTE format('DROP TABLE schema_%s.my_table',fyq); 
    end
$$;

Demo in sqldaddy.io

edited Nov 23 '22 at 10:13

answered May 20 '22 at 17:26

emrdev

2,155
3
9
15

Better use `%I` instead of `%s` for proper escaping ([demo](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ddc359bd452959b17069fe06d309cdf5)) – Bergi May 20 '22 at 18:16

score 0 · Answer 3 · answered May 23 '22 at 13:14

Just to add a third option that I came up with which combines the queries inside a single EXECUTE format(); statement, here's what I came up with:

CREATE OR REPLACE FUNCTION cnms_fy22q2.drop_summary_tables_function(
fyq text)
  RETURNS void
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE 
AS $BODY$
BEGIN
 -- logic
  EXECUTE format(
  'TRUNCATE TABLE schema_%s.my_table;
  DROP TABLE  schema_%s.my_table', fyq, fyq);
END;
$BODY$;

How to Run Multiple Dynamic Queries in a PostgreSQL Function

3 Answers3