Dynamically select column in PostgreSQL

Question

I want to select a column from a table, with the column name being the result of a query like the following:

-- This query returns a single value
with x as (
    select a from table1 where <condition>
)

-- my_function() yields a table
select x from my_function()

How do I do that?

Thank you very much.

You'll have to write two queries, where the second is constructed from the results of the first one. You can do that in client code or inside the database, for example using PL/pgSQL in a DO statement. — Laurenz Albe
– Laurenz Albe, Commented Mar 19, 2019 at 15:25
that's exactly what I'm trying to do. Could you provide a more explicit solution? Thanks. — Quy Vu Xuan
– Quy Vu Xuan, Commented Mar 19, 2019 at 16:52

Laurenz Albe · Accepted Answer · 2019-03-19 17:18:15Z

1

You could write it in SQL with a temporary function:

CREATE FUNCTION pg_temp.tablefunc()
   RETURNS SETOF my_function_result_type
   LANGUAGE plpgsql AS
$$DECLARE
   v_colname text;
BEGIN
   SELECT a INTO v_colname
   FROM table1
   LIMIT 1;

   RETURN QUERY EXECUTE
      format(E'SELECT %I\n'
             'FROM my_function()',
             v_colname);
END;$$;

SELECT * FROM pg_temp.tablefunc();

answered Mar 19, 2019 at 17:18

Laurenz Albe

257k22 gold badges310 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically select column in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related