How to use row values as columns in a SELECT statement in PostgreSQL?

Question

I've got a table structure with these three tables in my PostgreSQL 9.2.8 database:

I'm trying to figure out how to select an orders row and on the SAME row add some of the columns from order_points and points.

You can think of the points table like a list of all items that a person could purchase, where it's known internally by the abbrev and it costs amount.

The order_points table is each item purchased, so points.id == order_points.points_id, and amount would be similar to saying they bought 5 candy bars. It links to an orders via order_points.order_id == orders.id

When I select an order, I want to have a column for each abbrev that exists as well as the amount from the order_points table.

So if points has this:

 id | name      | abbrev | amount
 --------------------------------
  1 | Snickers  | sn     | 1.34
  2 | Milky Way | mw     | 1.73
  3 | Coffee    | cf     | 10.12

and order_points has this:

 id | order_id | points_id | amount
 ----------------------------------
  1 |        1 |         1 |     10
  2 |        1 |         3 |      1

Then when I get my row I want all the columns from the order, plus three extra columns. I don't want to list every single column shown in orders above but basically assume I just wanted 4 of them, plus all the points stuff I'd end up with this as a row of output:

 id | created    | due        | name  | sn | mw | cf
 ------------------------------------------------
  1 | 2018-04-21 | 2018-05-01 | Fooey | 10 |  0 |  1

I've no idea how to dynamically add a column with a name (the abbrev) from a table lookup.

Can you show us the desired output, not just the additional columsn you want — Evan Carroll, Apr 22 '18 at 05:36
You probably need the `crosstab` function of postgres. These posts should help: https://stackoverflow.com/questions/22886327/use-row-values-as-columns-in-postgresql and https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — pgngp, Apr 22 '18 at 05:44
@EvanCarroll No it's not unique. That's what points at the `id` of `points`. — Gargoyle, Apr 22 '18 at 05:46

Dimitar Spasovski · Accepted Answer · 2018-04-28T04:17:32.837

As Adam Silenko said, you cannot add a column at runtime. The best thing that you can do is to use 2 queries. One to a function that will create a temporary table with the columns you need and another one to query the table and get the results. This is explained here.

The function that will create the temporary table:

CREATE OR REPLACE FUNCTION get_order(orderId BIGINT)
  RETURNS VOID AS
  $$
  DECLARE column_names varchar[];
  DECLARE column_values float[];
  DECLARE final_select TEXT := 'SELECT id, name points_columns FROM orders where id=' || orderId;
  DECLARE create_table_statement TEXT := 'CREATE TEMP TABLE temp_result_table ON COMMIT DROP AS select_statement';
  DECLARE columns_values_concatenated TEXT := '';
  BEGIN
    SELECT array_agg(abbrev),  array_agg(CASE WHEN amount IS NULL THEN 0 ELSE amount END)
    into column_names, column_values FROM
     (SELECT abbrev, order_points.amount as amount FROM points LEFT JOIN order_points ON points.id = order_points.points_id and order_id = orderId
     ORDER BY points.id) points_amount;

     FOR i IN 1 .. array_upper(column_names, 1)
       LOOP
       columns_values_concatenated := columns_values_concatenated || ', ' || column_values[i] || ' as ' || column_names[i];
     end loop;
    final_select := replace(final_select, 'points_columns',columns_values_concatenated);
    create_table_statement:= replace(create_table_statement, 'select_statement', final_select);
    EXECUTE create_table_statement;
end;

$$ LANGUAGE Plpgsql;

We use 2 arrays, column_names and column_values to store the names ("sn", "mw", "cf) and values for those names for the selected order respectively.

We use those 2 arrays to generate the select statement (in the current code I am getting only the id and name from the orders table but you can easily change that). We store the select statement into the final_select variable. Finally we add the generated select statement to the create_table_statement and we create and fill the temporary table.

Now, as explained in the link above, because we need 2 queries to access the data, we have to execute both of the queries in a single transaction (in order to avoid name collisions if we are calling the function multiple times).

BEGIN;
SELECT * FROM get_order(1);
SELECT * FROM temp_result_table;
COMMIT; --The temporary table will be dropped on commit

Adam Silenko · Answer 2 · 2018-04-25T20:28:07.463

2

You can't dynamically add a column with unknown name. Names of columns in query must be known. But you can use output of one query to programmatically define the names of fields in output query.

edited Apr 25 '18 at 20:28

answered Apr 25 '18 at 20:06

Adam Silenko

3,025
1
14
30

Can you expand on that a bit more please? Are you saying I can do it from code, but not just SQL? – Gargoyle Apr 25 '18 at 23:26
exactly. read about crostab [tablefunc module](https://www.postgresql.org/docs/9.1/static/tablefunc.html). The best way to do this is use `crosstab(text source_sql, text category_sql)`, but you must know fields names _The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example:_ `SELECT * FROM crosstab('...', '...') AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);` – Adam Silenko Apr 25 '18 at 23:34
I'm just not seeing at all how I'd use that to solve this problem. – Gargoyle Apr 26 '18 at 04:02
i write only how it can be done, if you wanna some code, pleas give more details... – Adam Silenko Apr 26 '18 at 09:48
I don't know how to give any more details than I did in the question. – Gargoyle Apr 27 '18 at 04:53

How to use row values as columns in a SELECT statement in PostgreSQL?

2 Answers2