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.

Answer 1

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