Query with dynamic target columns

Question

I am trying to build a view in Postgres that uses 3 tables worth of data. I am not sure if this is possible and have searched around a bit on google but didn't turn up anything conclusive. This is what I am attempting to do:

I have a table of item names - lets say there are 5 items:

fruits

id | name
1  | banana
2  | orange
3  | pear
4  | apple
5  | grape

I then have a list of people

people

id  |  name
1   |  Joe Blow
2   |  Sally Smith
3   |  John Jones
4   |  Sam Benny
5   |  Nick Stevens
6   |  Peter Sandwitch
7   |  Sarah Morgan

I then have a third table linking the two above:

people_fruits

person_id | fruit_id
1         | 1
1         | 2
1         | 3
1         | 4
2         | 1
2         | 3
3         | 5
6         | 3
7         | 3
7         | 4

What I am trying to do is be able to dynamically create a view utilizing the above that will change the columns based on the content of the fruit table. for example, I would want the view to show the above data as follows:

my_fruity_view

name            | bananna | orange | pear | apple | grape
Joe Blow        | X       | X      | X    | X     |
Sally Smith     | X       |        | X    |       |
John Jones      |         |        |      |       | X
Sam Benny       |         |        |      |       |
Nick Stevens    |         |        |      |       |
Peter Sandwitch |         |        | X    |       |
Sarah Morgan    |         |        | X    | X     |

Then if I was to add the fruit mango at a later time, the next time the query was run (without modification), it would add that as a column:

my_fruity_view

name            | bananna | orange | pear | apple | grape | mango
Joe Blow        | X       | X      | X    | X     |       |
Sally Smith     | X       |        | X    |       |       |
John Jones      |         |        |      |       | X     |
Sam Benny       |         |        |      |       |       |
Nick Stevens    |         |        |      |       |       |
Peter Sandwitch |         |        | X    |       |       |
Sarah Morgan    |         |        | X    | X     |       |

Is such a query possible? I see a few things like this on stack overflow - but it seems like its done on a per column basis - but my data needs to be dynamic.

I can achieve this with programming but I would much prefer to pack it up into a view to keep things neat. any help on this would be appreciated.

Answer 1

Basically you want a pivot table or a cross tabulation. The additional module tablefunc provides the functionality you need. If you are not familiar with it, read this first:

• PostgreSQL Crosstab Query

The special difficulty of your case: you first need a query joining the tables to produce the right input:

SELECT p.name, f.name, text 'x' AS marker -- required, logically redundant column
FROM   people             p
LEFT   JOIN people_fruits pf ON pf.person_id = p.id  -- LEFT JOIN !
LEFT   JOIN fruits        f  ON f.id = pf.fruit_id
ORDER  BY p.id, f.id;  -- seems to be the desired sort order

LEFT [OUTER] JOIN, so you don't lose people without fruits.

Use it in a crosstab() function taking two parameters like this:

SELECT * FROM crosstab(
    $$SELECT p.name, f.name, text 'x'
      FROM   people             p
      LEFT   JOIN people_fruits pf ON pf.person_id = p.id
      LEFT   JOIN fruits        f  ON f.id = pf.fruit_id
      ORDER  BY p.id$$   
   ,$$VALUES ('bananna'), ('orange'), ('pear'), ('apple'), ('grape')$$)
AS ct (name text, bananna text, orange text, pear text, apple text, grape text);

The order of fruits in the target column list has to match the order of fruits in the 2nd parameter (ordered by id in your case).

Missing fruits get a NULL value.

However, this is not dynamic, yet. Completely dynamic is strictly not possible with SQL, which requires to know resulting columns at call time. One way or the other, you need two round trips to the DB server. You can let Postgres build the crosstab query dynamically and then execute it in the next step.

Related answers with code examples:

• Execute a dynamic crosstab query
• Dynamic alternative to pivot with CASE and GROUP BY

An alternative would be to return an array or a document type (json, xml, ...) that contains a dynamic list of elements.