Search code examples
sqlpostgresqlplpgsqldynamic-sqlnames

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

Solution

  • 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: