you must use AS (..) in crosstab query...
To actually, be able to create AS on "unkown" data in design time, what should produce an "unknown" result and unknown number of columns we are using Dynamic SQL to build AS part... (but of course function can't return result - just for export to csv purposes...)
Inside PL/R you can take the same table as it is (unpivoted) as your data.frame and then pivot it inside R using reshape package,,, And then inside PL/R function do whatever you would like to do with data i.e export to whatever... - but you cant return it pivoted as result of Postgres function..
Kind Regards,
Misa
2012/9/4 Aram Fingal <fingal@xxxxxxxxxxxxxxxxxx>
So, are you saying that if I do something like this:
On Sep 4, 2012, at 3:26 PM, Joe Conway wrote:
> On 09/04/2012 12:17 PM, Aram Fingal wrote:
>> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
>>> see the documentation for Additional Supplied Modules, in your
>>> case tablefunc :
>>>
>>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
>>
>> I evaluated tablefunc about a year and a half ago and found that it
>> was not what I wanted because you have to explicitly list what you
>> want the columns to be. In some cases, there will be hundreds of
>> columns in the pivoted table. The Reshape library in R can pivot
>> tables without you even knowing ahead of time how many columns there
>> are going to be.
>
> Sure, but you cannot return that reshaped table to postgres without
> specifying the list of columns explicitly. That is because of how
> postgres works internally and has nothing to do with whether you are
> using crosstab from tablefunc, PL/R, or some hand-coded SQL statement to
> build your crosstab.
>
> But certainly if you can do all your work on the reshaped table within
> the R environment, PL/R will be easier to use.
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?
-Aram
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
