Re: dynamic crosstab

On 01/31/2010 03:52 PM, Florent THOMAS wrote:
> Hello everybody,
>
> I'm trying to find out how to have a dynamic crosstab as in excel,
> ireport,etc...
> As i understand of the manual here :
> http://docs.postgresqlfr.org/8.4/tablefunc.html
> I can have multiple columns.
>
> Unfortunately, it seems indispensible to name the columns in the AS clause.
> Am I right or is ther a way to let the query generate the columns and
> there name without naming them?

Wow, second time this week this has come up. Maybe it ought to be an FAQ.

Anyway, your best bet is to use crosstab from contrib/tablefunc, and
wrap it with application code that dynamically executes the query with
the needed column definitions. It is a simple two step process:

Using crosstab(text source_sql, text category_sql),

- first execute category_sql to get a list of columns
- dynamically build the complete crosstab SQL including the columns
- execute the crosstab SQL

The parser/planner requires the column type information because the
result is potentially filtered (WHERE clause) or joined (FROM CLAUSE)
with other relations. There is no way around this, at least not
currently, and probably not ever in this form. If PostgreSQL ever
supports true procedures (i.e. CALL sp_crosstab(...)), then it would be
possible to forego the column definitions as joining and filtering would
not be possible in that scenario.

Joe