Re: crosstab

No,

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(at)multifactorial(dot)com>

>
> 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.
>
>
> 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?
>
> -Aram
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>