4

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

enter image description here

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.

Improve this question
7
  • Can you show us the desired output, not just the additional columsn you want Commented Apr 22, 2018 at 5:36
  • I added a few of the columns into the example from orders Commented Apr 22, 2018 at 5:40
  • You probably need the crosstab function of postgres. These posts should help: stackoverflow.com/questions/22886327/… and stackoverflow.com/questions/3002499/postgresql-crosstab-query/… Commented Apr 22, 2018 at 5:44
  • is order_points.point_id UNIQUE? Commented Apr 22, 2018 at 5:45
  • how many rows are in points? Commented Apr 22, 2018 at 5:46

2 Answers 2

Reset to default
5
+50

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

5 Comments

Can you expand on that a bit more please? Are you saying I can do it from code, but not just SQL?
exactly. read about crostab tablefunc module. 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);
I'm just not seeing at all how I'd use that to solve this problem.
i write only how it can be done, if you wanna some code, pleas give more details...
I don't know how to give any more details than I did in the question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.