1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Christian Maíz
  • 113
  • 1
  • 4
  • Could you include an example of the input and desired output, and explain what happens with your current attempt? – IMSoP Jul 22 '14 at 15:58
  • The output would be the value in the table from the field in the array.. the above function should process something like.. select t.field1 into _row from table t; select t.field2 into _row from table t; select t.field3 into _row from table t; those fields are extracted from the array – Christian Maíz Jul 22 '14 at 16:00
  • Ah, OK, so by "field" you mean "column", and what you're after is to dynamically create the SQL to reference different columns each time round the loop. – IMSoP Jul 22 '14 at 16:38
  • yeah!.. exactly!.. I'll correct.. I'm talking about the column.. How can I select those column from the array? – Christian Maíz Jul 22 '14 at 17:11
  • There is no function header? I don't expect a `DO` statement producing notices is the goal? You need to define what you want to return. A set of single values? A set of rows? Arrays? Of what data type exactly? Please [edit](http://stackoverflow.com/posts/24892161/edit) your question. Do not squeeze essential information into comments. – Erwin Brandstetter Jul 22 '14 at 23:56

1 Answers1

2

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • hello, this answer helped me a lot with an unrelated problem, specifically `%1$L` and `%1$I` when trying to use `for loop` variable as a column name in a `select` query. could you point me to a good documentation where I could read further about, as you call them, *specifiers*? thanx – Scaramouche Dec 01 '18 at 16:53
  • 1
    @Scaramouche: Some related answers: https://stackoverflow.com/a/28152529/939860 and https://stackoverflow.com/a/10711349/939860. But I already linked to the documentation above. Follow the link to the manual for [`format()`](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT). – Erwin Brandstetter Dec 01 '18 at 23:54