Обсуждение: crosstab

hi all
How could i use crostab to display variable number of columns. in the output
There could be variable number of columns
Regards
Punnoose



--
View this message in context: http://postgresql.1045698.n5.nabble.com/crosstab-tp5722632.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Has anyone tried developing a PLR procedure to do the equivalent of crosstab but using the Reshape library from R?  The
reasonI ask is that I have been extracting tables like this with an R script, using RPostgreSQL, then functions from
Reshapeand writing the output to a .csv file.  I wonder if a PLR procedure could so this with fewer lines of code and
greaterspeed.  I'm just not clear on how to write the PLR code. 

Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
> hi all
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns

see the documentation for Additional Supplied Modules, in your case
tablefunc :

http://www.postgresql.org/docs/9.1/static/tablefunc.html


--
Vincent Veyron
http://marica.fr/
Gestion informatique des sinistres d'assurances et des dossiers contentieux pour le service juridique

On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:

> Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
>> hi all
>> How could i use crostab to display variable number of columns. in the output
>> There could be variable number of columns
>
> 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
explicitlylist 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. Maybe I need to revisit the documentation in case I missed something or if new functionality has been added since I
lastlooked.   

-Aram

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.

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

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
shownin the examples in the tablefunc documentation? 

-Aram

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.

Joe



--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

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/Rprocedure which, for example, pivots tables and then uses the write.table() function of R to send the results to
diskwithout returning any rows to PostgreSQL?  Such a procedure might prove be faster and more convenient than
extractingthe data from PostgreSQL into an R application layer and then writing to disk.  

-Aram

On Sep 4, 2012, at 4:30 PM, Aram Fingal wrote:

>
> On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:
>
>> Inside PL/R you can take the same table as it is (unpivoted) as your data.frame and then pivot it inside R using
reshapepackage,,, And then inside PL/R function do whatever you would like to do with data i.e export to whatever... -
butyou cant return it pivoted as result of Postgres function.. 
>
>  Okay, you just answered my question in reply to Joe Conway (crossed in the mail).  So I could just use write.table()
fromR to save to disk or I might even have the PL/R procedure write a heatmap or PCA plot to disk, again without
returningany rows to PostgreSQL. 
>
> -Aram

Or you could return the heatmap/plot as BYTEA data or use arrays as necessary.

Cheers,
M

On Tue, Sep 4, 2012 at 10:39 AM, punnoose
<punnoose.pj@dwisesolutions.com> wrote:
> hi all
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns
> Regards
> Punnoose

No.   The workaround I use is to write a query generator in pl/pgsql
(you can also do it in the client) which queries the specific columns
that are going to be crosstabbed and generates a query textually.
That query is returned to the client and executed, essentially
bypassing the restriction.   The way functions are implemented in
postgres forces them to have a rigidly defined set of output
columns...it's annoying and everybody hates it but that's the way
things have to be for various reasons.

Looking to the future, stored procedures might not end up having this
restriction depending on how they are implemented.  Now that LATERAL
and standalone backends are or are in the process of being knocked out
I consider stored procedures to be one of the great unsolved features
of postgres.

merlin

On 09/04/2012 01:23 PM, Aram Fingal wrote:
> 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.

As mentioned somewhere else along this thread, I don't know that it will
be faster, but it may well be more convenient if your goal is to get a
CSV file in the end anyway.

Joe


--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

On 09/04/2012 02:59 PM, Aram Fingal wrote:
> On Sep 4, 2012, at 4:36 PM, A.M. wrote:
>
>> Or you could return the heatmap/plot as BYTEA data or use arrays as
>> necessary.
>
> I was actually thinking exactly the same thing.  Part of the reason I
> use PostgreSQL for all my bioinformatics work is that there is a need to
> correctly associate analysis results with the data and experimental
> methods they come from.  I have tables for experimental runs,
> technicians, procedures, samples, drugs, etc. and I use foreign key
> constraints to connect them all.  The idea is to have all the
> information readily accessible to reproduce complex results in modern
> scientific fashion.  If I store the plots in the DB, I can connect them
> to all these basic information tables.

You can return your results (or some intermediate) object in serialized
form as bytea from a PL/R function and store it in a table along with
the basic experimental info. Then later if you pass the serialized
object back into another PL/R function as a bytea argument, it gets
reconstituted as the original R object.

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

(what btw forced me to check it in plv8 - and it seems they use the same approach internally in behind :(  - though doesn't make sense because of find_function works just with plv8 postgres functions) 

On 09/04/2012 04:45 PM, Misa Simic wrote:
> Hi Joe,
>
> Do you maybe know, is it possible inside PL/R to call another PL/R
> function - but take result as R object (whatever R function returns)?

Mmmm, what about using plr_modules table?

It creates native R functions that get installed into your R interpreter
when R is first initialized.

http://www.joeconway.com/plr/doc/plr-module-funcs.html

Joe




--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support