CODEWITHSUNDEEP
arraysfunctionpostgresqlplpgsqldynamic-sql
Christian Maíz
Christian Maíz

Reputation: 113

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

I need query a table getting the columns name from an array... something like this

$$
DECLARE
   column varchar[] := array['column1','column2','column3'];
   _row record;
BEGIN
    FOR i IN 1 .. array_length(column, 1)
    LOOP
       RAISE NOTICE '%: %', i, column[i];
       select t.column[i] into _row from table t;
       RAISE NOTICE '%: %', i, _row.column[i];
    END LOOP;
END;
$$ language plpgsql;

Did you get it? Is it possible?

Upvotes: 1

Views: 4332

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659197

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:

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.

Upvotes: 2

Related Questions