INSERT with dynamic column names

Question

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

Please explain what you are trying to achieve. There'll be a solution. And always disclose your version of Postgres. I seriously doubt that you work with Postgres 8.1, even though you link to that version of the manual. — Erwin Brandstetter, Jun 19 '16 at 00:45

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

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:

User defined variables in PostgreSQL

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:

edited May 23 '17 at 12:08

Community

1
1

answered Jun 19 '16 at 00:36

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you for information, I was looking for that solution. I have one more question related to this topic. I have array cols, which contain names of columns. array -------------- {"name,age"} (1 row), how I can pass that array to f_insert_these_columns function? When I have tried select f_insert_these_columns(VARIADIC cols), I have got error there is no column name cols. – Piotr Olkiewicz Jun 19 '16 at 10:48
@user2706590: The syntax for an array literal with two elements (`'name'` and `'age'`) is `'{name,age}'`. What you display (`'{"name,age"}'`) is an array with a *single* element `'name,age'`. – Erwin Brandstetter Jun 19 '16 at 11:30
When I have used syntax '{name, age}' it works, but I would like pass variable named cols to function. Something like f_insert_columns(VARIADIC cols). I corrected something and when I run select * from cols I get: array ------------ {name,age} (1 row) , but still i have got the same error after running f_insert_columns(VARIADIC cols). error: column cols does not exist. – Piotr Olkiewicz Jun 19 '16 at 11:47
@user2706590: Please start a new question to show the *exact* code and data you use; and your exact version of Postgres as well as the verbatim error message. The error message you display is not possible with the code I supplied. – Erwin Brandstetter Jun 19 '16 at 11:53
Ok, I have posted new question with full code here: http://stackoverflow.com/questions/37907182/copy-records-with-dynamic-column-names – Piotr Olkiewicz Jun 19 '16 at 12:26

INSERT with dynamic column names

1 Answers1