How to get columns name from an array for a select query in Postgres

Whenever you need to convert user input to identifiers or code in an SQL statement, you need dynamic SQL. Either concatenate the statement in your client an send it to the DB engine or (more efficiently) do it in PL/pgSQL (or some procedural server-side language) dynamically with EXECUTE. More details:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

Solution

CREATE OR REPLACE FUNCTION f_get_columns(_cols text[])
  RETURNS TABLE (col text, val text) AS
$func$
DECLARE
   _col text;
BEGIN
   FOREACH _col IN ARRAY _cols LOOP
      RETURN QUERY EXECUTE
      format('SELECT %1$L::text, %1$I::text FROM tbl t', _col);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_array_of_columns('{column1,column2}');

Returns (random values):

col     | val
--------+-----
column1 | 001
column1 | 002
column1 | 003
column2 | {foo,boo,foo}
column2 | {"",for,test}
column2 | {test,foo,boo}

Key elements:

• A matching RETURNS declaration.
• A way to ruturn your results, RETURN QUERY EXECUTE here.
• A FOREACH loop in this particular case.
• format() to simplify string concatenation and escape names and values properly.
  Note the specifiers:
  %1$L ... first parameter as quoted literal.
  %1$I ... first parameter as properly escaped Identifier.

Note how I cast both columns to text (::text) to match the return type and make it work for any data type. Depending on exact requirements, this could be more specific.

Exactly, I need dynamic SQL, but I don't need to process it from the user input, the dynamic SQL would be built from an array where the columns names are stored, the value in the columns I have to store in a variable , something like:

CREATE OR REPLACE FUNCTION test()
  RETURNS integer AS
$func$
DECLARE
    ok integer := 0;
    cols varchar[] := array['col1','col2','col3','col4','col5'];
    _row record;
BEGIN

    FOR i IN 1 .. array_length(cols, 1)
    LOOP

        -- I wanna know how to make a dynamic sql for this purpose
       select t.cols[i] into _row -- for cols[1] this would build a sql like, "selec t.col1 into _row from table t"
       from table t;

       insert into x values (_row); -- then use that variable "_row" and insert in another table

    END LOOP;

    return ok;
END
$func$ LANGUAGE plpgsql;

Another tip: I'm using PostgreSQL 8.4, so I can't use FOREACH statement