Is it possible to create a dynamic join? I'm using PostgreSQL if it matters.



Assuming these two tables:

 id | run
----+-------
  1 | run_1
  2 | run_2
  3 | run_3


and

sample | elapsed |  run
-------+---------+-------
 samp1 | 1:09    | run_1
 samp2 | 0:32    | run_1
 samp1 | 0:58    | run_2
 samp2 | 0:28    | run_2
 samp1 | 0:55    | run_3
 samp2 | 0:28    | run_3


I want the following output:

sample | run_1 | run_2 | run_3
-------+-------+-------+-------
 samp1 | 1:09  | 0:58  | 0:55
 samp2 | 0:32  | 0:28  | 0:28


This is easy enough to do if I have a fixed number of run values, but is it possible to create a dynamic query which will output the right thing regardless of the number of runs?
Basically it's a crosstab query:


PostgreSQL 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 (json, hstore, ...).
Return the maximum possible number of columns and fill dangling columns with NULL values.


I have answered similar questions on SO before:


Dynamic alternative to pivot with CASE and GROUP BY
Refactor a PL/pgSQL function to return the output of various SELECT queries