Piotr Olkiewicz Piotr Olkiewicz - 5 months ago 19
SQL Question

INSERT with dynamic column names

I have column names stored in variable

colls
, next I execute code:

DO $$
DECLARE
v_name text := quote_ident('colls');
BEGIN
EXECUTE 'insert into table1 select '|| colls ||' from table2 ';
-- EXECUTE 'insert into table1 select '|| v_name ||' from table2 ';
END$$;


I have got error: column "colls" does not exist. Program used
colls
as name not as variable. What am I doing wrong?

I have found similar example in documentation:

https://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Answer

I have column names stored in variable colls

No, you don't. You have a variable v_name - which holds a single word: 'colls'. About variables in SQL:

Read the chapters Identifiers and Key Words and Constants in the manual.

And if you had multiple column names in a single variable, you could not use quote_ident() like that. It would escape the whole string as a single identifier.


I guess the basic misunderstanding is this: 'colls' is a string constant, not a variable. There are no other variables in a DO statement than the ones you declare in the DECLARE section. You might be looking for a function that takes a variable number of column names as parameter(s) ...

CREATE OR REPLACE FUNCTION f_insert_these_columns(VARIADIC _cols text[])
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'INSERT INTO table1 SELECT '
          || string_agg(quote_ident(col), ', ')
          || ' FROM table2'
      FROM   unnest(_cols) col
      );
END
$func$  LANGUAGE plpgsql;

Call:

SELECT f_insert_these_columns('abd', 'NeW Deal');          -- column names case sensitive!
SELECT f_insert_these_columns(VARIADIC '{abd, NeW Deal}'); -- column names case sensitive!

Note how I unnest the array of column names and escape them one by one.
A VARIADIC parameter should be perfect for your use case. You can either pass a list of column names or an array.
Either way, be vary of SQL injection.
Related, with more explanation: