CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sqlnames
Piotr Olkiewicz
Piotr Olkiewicz

Reputation: 25

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

Upvotes: 1

Views: 1894

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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:

Upvotes: 2

Related Questions