Basically it's a crosstab query:
The dynamic result type is a problem, though.
... is it possible to create a dynamic query which will output the right thing regardless of the number of runs?
No. Currently (including Postgres 9.6) not possible with a single
SELECT statement. Not unless you know the return type at call time at the latest.
SQL is a strictly typed language. Postgres demands to know the row type that is going to be returned from the query. Either provided by user input in one or the other form, or from information in the system catalog (registered tables and types). Deducing the return type from involved data dynamically is strictly not possible.
Then why does
SELECT * FROM tbl work?
Because there is a row type registered for every table (view, materialized view etc.) in the system catalogs, and Postgres knows which columns to return by looking that up.
Which ever way you try, you need two round trips to the database server for your query with dynamic result type:
Determine the number of columns in the result and build the
SELECT statement accordingly.
Execute said statement returning the now well known row type.
Alternatives would be:
- Return two arrays instead: One containing column names, the other containing values.
- Return a document type (
- Return the maximum possible number of columns and fill dangling columns with NULL values.
I have answered similar questions on SO before: