plpgsql dynamic insert with tablename as variable and array variable in where clause

Question

I am trying to write a function that runs an insert statement with table name as variable and the where clause accepts an array as another variable.

CREATE OR REPLACE FUNCTION f_dynamic_sql()
  RETURNS void
  LANGUAGE plpgsql
AS
$body$

DECLARE 
rec record;
iterator float4 := 1;
tbl_name text;

BEGIN
DROP TABLE IF EXISTS t_ar; CREATE TEMP TABLE t_ar (reg text, zones text[]);
INSERT INTO t_ar VALUES 
('NA', '{"US","UG","UC","UR"}'),
('NE', '{"UK", "SP"}'),
('LA', '{"CA","EC","WC","EC","WC"}');

FOR rec IN SELECT zones from t_ar
    LOOP 
  tbl_name := 'schema.table_' || iterator; 
  EXECUTE format('INSERT INTO %s 
          SELECT
            DATE_PART(''MONTH'', month)::INT AS month,
            SUM(COALESCE(prev_year,0))::INT AS py,
            SUM(COALESCE(last_year,0))::INT AS ly
        FROM org_table
        WHERE load_area IN (SELECT UNNEST(rec.zones))
        GROUP BY 1
        ORDER BY 1', tbl_name); 

    iterator := iterator + 1;
END LOOP;
END;
$body$
  VOLATILE
  COST 100;

This runs fine out of execute format but then I cannot put the table name as variable but inside execute format it shows syntax error in the SQL query. The SQL query runs alright outside.

The temporary table and the FOR loop seem pretty pointless. If you simplified your actual code, did you verify that the function you posted creates the error. And, most of all, please copy and paste the complete error messages with all details, hints and context. — Laurenz Albe

klin klin · Accepted Answer · 2019-02-19T00:11:35

You need to use placeholders and arguments in the format() function.

A practical tip when using EXECUTE format()- use RAISE NOTICE '%', instead of EXECUTE and run the function in psql to see what queries it actually generates. When you are sure that the generated queries are correct, replace RAISE NOTICE '%', with EXECUTE. Example:

...
FOR rec IN SELECT reg, zones FROM t_ar
LOOP 
RAISE NOTICE '%', format('
    INSERT INTO my_schema.table_%s 
    SELECT
        DATE_PART(''MONTH'', month)::INT AS month,
        SUM(COALESCE(prev_year,0))::INT AS py,
        SUM(COALESCE(last_year,0))::INT AS ly
    FROM org_table
    WHERE load_area = ANY (%L)
    GROUP BY 1
    ORDER BY 1', rec.reg, rec.zones); 
END LOOP;
...

plpgsql dynamic insert with tablename as variable and array variable in where clause

1 Answers