Postgres select rows from a table using column names specified in another



  • I'm using Postgres 11.14 I have a table with a list of column names in it.

    I want to create a function that

    • executes a query that returns a result set containing a row with a column for each of the column names in the table.
    • then loops through all of the columns in the table and returns a row containing the column name and the content of that column in the result set.

    so for example.

    column_table:

    |column_name|
     |a|
     |b|
     |c|
    

    Function pseudocode:

    insert into record_set select z+y as a, b, x / w as c from another_table limit 1;
    

    for column_name in select column_name from column_table
    loop
    select into result column_name, record_set.column_name -- recordset column with the name of the contents of the variable column_name
    end loop;
    return result

    I've tried several approaches, but have been unable to get it right.



  • The best way to achieve that, is by using psql's feature \gexec:

    bench=# with columns(names) as (
      select column_name
      from information_schema.columns
      where table_schema = 'public'
      and table_name = 'pgbench_accounts'
      order by ordinal_position asc 
      limit 3 
    )
    select 'select ' || string_agg(names, ',') || ' from pgbench_accounts limit 10' 
    from columns
    ;
                            ?column?                        
    --------------------------------------------------------
     select aid,bid,abalance from pgbench_accounts limit 10
    (1 row)
    

    bench=# with columns(names) as (
    select column_name
    from information_schema.columns
    where table_schema = 'public'
    and table_name = 'pgbench_accounts'
    order by ordinal_position asc
    limit 3
    )
    select 'select ' || string_agg(names, ',') || ' from pgbench_accounts limit 10' from columns \gexec
    aid | bid | abalance
    -----+-----+----------
    13 | 1 | 0
    16 | 1 | 0
    33 | 1 | 0
    42 | 1 | 0
    49 | 1 | 0
    54 | 1 | 0
    55 | 1 | 0
    60 | 1 | 1722
    34 | 1 | -867
    8 | 1 | 4394
    (10 rows)

    bench=#

    Or you can use pl/pgsql (or any other language, regarding the extensions you installed, to perform dynamic SQL, but it feels like trying to kill a fly with a hammer).


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2