Re: crosstab

On Sep 4, 2012, at 3:56 PM, Joe Conway wrote:

> On 09/04/2012 12:48 PM, Aram Fingal wrote:
>> So, are you saying that if I do something like this:
>>
>> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
>>
>> Then I don't have to list what the columns are going to be? In other
>> words, I can skip the "AS (...)" clause which is shown in the
>> examples in the tablefunc documentation?
>
> No, sorry, but that is not what I'm saying :-(
>
> Wen you run
>
> copy ("some query") to ...
>
> it still requires postgres to execute "some query" and the standard
> grammar rules will be applied. Postgres must be able to resolve data
> types for the columns in the result, and therefore it needs you to
> provide a column definition either at function creation time (via OUT
> params or by explicit composite return type) or at execution time via
> AS(...) clause.

So then, PL/R is not a solution to being able to pivot tables directly in PostgreSQL but I might be able to define a PL/R procedure which, for example, pivots tables and then uses the write.table() function of R to send the results to disk without returning any rows to PostgreSQL? Such a procedure might prove be faster and more convenient than extracting the data from PostgreSQL into an R application layer and then writing to disk.

-Aram