Dynamically generate the AS clause for Postgres crosstab

Question

This is actually a follow up to Dynamically generate columns for crosstab in PostgreSQL

To be clear: if I have a dataset with a variable number of keys I cannot generate the AS clause?

Eg. A table with tests (testdate, pupil, result) initially with the pupils (Tom, Dick, Harry)

The crosstab for this would be:

select * from crosstab(
'select testdate, pupil, result from tests)',
'select distinct pupil from tests order by pupil')
as ct
(  
"testdate" text,
"Dick" text,
"Harry" text,
"Tom" text)

Will fail as soon as Sally joins? With something like

ERROR:  invalid return type
DETAIL:  Query-specified return tuple has 4 columns but crosstab returns 5.

score 0 · Answer 1 · answered Jun 09 '13 at 07:22

PostgreSQL needs to know the return types and structure during planning, so you cannot return a dynamic number of columns directly. Typically you want to do one of two things:

1: Constrain your results

This approach would be something like:

select * from crosstab(
'select testdate, pupil, result from tests)',
'select distinct pupil from tests where pupil in (''Dick'', ''Harry'', ''Tom'') order by pupil')
as ct
(  
"testdate" text,
"Dick" text,
"Harry" text,
"Tom" text)

That way Sally just won't be found when she shows up.

2: Return a text refcursor, json, or xml

The other way around this is to return a type which can store various data, and then EXECUTE these within a function or stored procedure which dynamically calculates the return columns. This is slightly more complex and so an example is not really in the question here. See the PL/PGSQL functions for details though.

The key thing though is that you need to return each row as a single entity. You could use row notation or you could return a refcursor (which must be FETCHED in the application in the same transaction, note this does not work well with pgAdmin), or you need to return a JSON or XML type. Note that rows can be converted to JSON and XML without too much trouble depending on your PostgreSQL version (JSON requires PostgreSQL 9.1 or higher, possibly with an addon).

Dynamically generate the AS clause for Postgres crosstab

1 Answers1