Re: dynamic crosstab

On 01/28/2010 08:57 AM, Andy Colson wrote:
>>
>> How do you feel about a little perl? It would be pretty simple, and
>> could generate a csv based on any resultset (any number of columns). I'd
>> be happy to post a little get you started code if you wanted.

If you're going to go through all that, I don't understand why you
wouldn't just 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
- build the complete crosstab SQL including the columns
- execute the crosstab SQL

The fact is that, as has been stated, the parser/planner requires the
column type information because the result is potentially filtered or
joined 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 are
not possible.

Joe