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
Attachment:
signature.asc
Description: OpenPGP digital signature
