Thread: crosstab

crosstab

From
punnoose
Date:
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.


Re: crosstab

From
dinesh kumar
Date:
Hi , 

I believe, we need to have a Dynamic SQL for this to generate the dynamic Crostab.. I have tried the same earlier and wrote the below Calendar Query for the year 2011 including holidays. 

Crostab Example 
=============
select
min(case when extract(month from d.dd)= 1 then
'Jan'
when extract(month from d.dd)= 2 then
'Feb'
when extract(month from d.dd)=3 then
'Mar'
when extract(month from d.dd)= 4 then
'Apr'
when extract(month from d.dd)= 5 then
'May'
when extract(month from d.dd)= 6 then
'Jun'
when extract(month from d.dd)= 7 then
'Jul'
when extract(month from d.dd)= 8 then
'Aug'
when extract(month from d.dd)=9 then
'Sep'
when extract(month from d.dd)= 10 then
'Oct'
when extract(month from d.dd)= 11 then
'Nov'
when extract(month from d.dd)= 12 then
'Dec'
end
) as MONTH,min(case when extract(DOW from d.dd)=0 then
(extract(day from d.dd))
end) as SUN,
min(case when extract(DOW from d.dd)=1 then
(extract(day from d.dd))
end) as MON,
min(case when extract(DOW from d.dd)=2 then
(extract(day from d.dd))
end) as TUE,
min(case when extract(DOW from d.dd)=3 then
(extract(day from d.dd))
end) as WED,
min(case when extract(DOW from d.dd)=4 then
(extract(day from d.dd))
end) as THU,
min(case when extract(DOW from d.dd)=5 then
(extract(day from d.dd))
end) as FRI,
min(case when extract(DOW from d.dd)=6 then
(extract(day from d.dd))
end) as SAT,
min(case when extract(day from d.dd)=26 and extract(month from d.dd)=1 then
'Republic Day <---> 26th January 2011 <--> Wednessday'
when extract(day from d.dd)=16 and extract(month from d.dd)=2 then
'Id-E-Milad <--> 16th February 2011 <--> Wednessday'
when extract(day from d.dd)=19 and extract(month from d.dd)=2 then
'Chhatrapati Shivaji Maharaj Jayanthi <--> 19th February 2011 <--> Saturday'
when extract(day from d.dd)=2 and extract(month from d.dd)=3 then
'Mahashivratri <--> 2nd March 2011<--> Wednessday'
when extract(day from d.dd)=4 and extract(month from d.dd)=4 then
'Gudi Padwa <--> 4th April 2011 <--> Tuesday'
when extract(day from d.dd)=12 and extract(month from d.dd)=4 then
'Ram Navmi - 12th April - Tuesday'
when extract(day from d.dd)=14 and extract(month from d.dd)=4 then
'Dr. Babasaheb Ambedkar Jayanthi - 14th April - Thursday'
when extract(day from d.dd)=16 and extract(month from d.dd)=4 then
'Mahavir Jayanthi - 16th April - Saturday'
when extract(day from d.dd)=22 and extract(month from d.dd)=4 then
'Good Friday - 22nd April - Friday'
when extract(day from d.dd)=17 and extract(month from d.dd)=5 then
'Buddha Pournima - 17th May - Tuesday'
when extract(day from d.dd)=15and extract(month from d.dd)=8 then
'Independence day - 15th August - Monday'
when extract(day from d.dd)=19 and extract(month from d.dd)=8 then
'Parsi New Year - 19th August - Friday'
when extract(day from d.dd)=31 and extract(month from d.dd)=8 then
'Ramzan Id(Id-Ul-Fitar)(Shawal-1) - 31st August - Wednessday'
when extract(day from d.dd)=1 and extract(month from d.dd)=9 then
'Ganesh Chaturthi - 1st September - Thursday'
when extract(day from d.dd)=6 and extract(month from d.dd)=10 then
'Dasara - 6th October - Thursday'
when extract(day from d.dd)=26 and extract(month from d.dd)=10 then
'Diwali Amavasya (Laxmi Pujan) - 26th October - Wednessday'
when extract(day from d.dd)=27 and extract(month from d.dd)=10 then
'Diwali (Balipratipada) - 27th October - Thursday'
when extract(day from d.dd)=7 and extract(month from d.dd)=11 then
'Bakri Id (iD-Ul-Zun) - 7th November - Monday'
when extract(day from d.dd)=10 and extract(month from d.dd)=11 then
'Guru Nanank Jayanthi - 10th November - Thursday'
when extract(day from d.dd)=6 and extract(month from d.dd)=12 then
'Moharam - 6th December - Tuesday'
when extract(day from d.dd)=20 and extract(month from d.dd)=3 then
'Holi - 20th March - Sunday'
when extract(day from d.dd)=1 and extract(month from d.dd)=5 then
'Maharastra Day - 1st May - Sunday'
when extract(day from d.dd)=2 and extract(month from d.dd)=10 then
'Mahatma Gandhi Jayanthi - 2nd October - Sunday'
when extract(day from d.dd)=25 and extract(month from d.dd)=12 then
'Christmas - 25th December - Sunday'

end) as Holidays
from
(
select
date_trunc('year',current_date)::date + a.si - 1 as dd
from
(
select generate_series(1,cast((extract('day' from date_trunc('year',current_date)-date_trunc('year',current_date-365))) as int)) as si) as a
) as d
group by extract(week from d.dd),extract(month from d.dd)
order by extract(month from d.dd),1,2,3,4,5,6,7;


OUTPUT
========

month | sun | mon | tue | wed | thu | fri | sat | holidays
-------+-----+-----+-----+-----+-----+-----+-----+----------------------------------------------------------------------------
Jan | 2 | | | | | | 1 |
Jan | 9 | 3 | 4 | 5 | 6 | 7 | 8 |
Jan | 16 | 10 | 11 | 12 | 13 | 14 | 15 |
Jan | 23 | 17 | 18 | 19 | 20 | 21 | 22 |
Jan | 30 | 24 | 25 | 26 | 27 | 28 | 29 | Republic Day <---> 26th January 2011 <--> Wednessday
Jan | | 31 | | | | | |
Feb | 6 | | 1 | 2 | 3 | 4 | 5 |
Feb | 13 | 7 | 8 | 9 | 10 | 11 | 12 |
Feb | 20 | 14 | 15 | 16 | 17 | 18 | 19 | Chhatrapati Shivaji Maharaj Jayanthi <--> 19th February 2011 <--> Saturday
Feb | 27 | 21 | 22 | 23 | 24 | 25 | 26 |
Feb | | 28 | | | | | |
Mar | 6 | | 1 | 2 | 3 | 4 | 5 | Mahashivratri <--> 2nd March 2011<--> Wednessday
Mar | 13 | 7 | 8 | 9 | 10 | 11 | 12 |
Mar | 20 | 14 | 15 | 16 | 17 | 18 | 19 | Holi - 20th March - Sunday
Mar | 27 | 21 | 22 | 23 | 24 | 25 | 26 |
Mar | | 28 | 29 | 30 | 31 | | |
Apr | 3 | | | | | 1 | 2 |
Apr | 10 | 4 | 5 | 6 | 7 | 8 | 9 | Gudi Padwa <--> 4th April 2011 <--> Tuesday
Apr | 17 | 11 | 12 | 13 | 14 | 15 | 16 | Dr. Babasaheb Ambedkar Jayanthi - 14th April - Thursday
Apr | 24 | 18 | 19 | 20 | 21 | 22 | 23 | Good Friday - 22nd April - Friday
Apr | | 25 | 26 | 27 | 28 | 29 | 30 |
May | 1 | | | | | | | Maharastra Day - 1st May - Sunday
May | 8 | 2 | 3 | 4 | 5 | 6 | 7 |
May | 15 | 9 | 10 | 11 | 12 | 13 | 14 |
May | 22 | 16 | 17 | 18 | 19 | 20 | 21 | Buddha Pournima - 17th May - Tuesday
May | 29 | 23 | 24 | 25 | 26 | 27 | 28 |
May | | 30 | 31 | | | | |
Jun | 5 | | | 1 | 2 | 3 | 4 |
Jun | 12 | 6 | 7 | 8 | 9 | 10 | 11 |
Jun | 19 | 13 | 14 | 15 | 16 | 17 | 18 |
Jun | 26 | 20 | 21 | 22 | 23 | 24 | 25 |
Jun | | 27 | 28 | 29 | 30 | | |
Jul | 3 | | | | | 1 | 2 |
Jul | 10 | 4 | 5 | 6 | 7 | 8 | 9 |
Jul | 17 | 11 | 12 | 13 | 14 | 15 | 16 |
Jul | 24 | 18 | 19 | 20 | 21 | 22 | 23 |
Jul | 31 | 25 | 26 | 27 | 28 | 29 | 30 |
Aug | 7 | 1 | 2 | 3 | 4 | 5 | 6 |
Aug | 14 | 8 | 9 | 10 | 11 | 12 | 13 |
Aug | 21 | 15 | 16 | 17 | 18 | 19 | 20 | Independence day - 15th August - Monday
Aug | 28 | 22 | 23 | 24 | 25 | 26 | 27 |
Aug | | 29 | 30 | 31 | | | | Ramzan Id(Id-Ul-Fitar)(Shawal-1) - 31st August - Wednessday
Sep | 4 | | | | 1 | 2 | 3 | Ganesh Chaturthi - 1st September - Thursday
Sep | 11 | 5 | 6 | 7 | 8 | 9 | 10 |
Sep | 18 | 12 | 13 | 14 | 15 | 16 | 17 |
Sep | 25 | 19 | 20 | 21 | 22 | 23 | 24 |
Sep | | 26 | 27 | 28 | 29 | 30 | |
Oct | 2 | | | | | | 1 | Mahatma Gandhi Jayanthi - 2nd October - Sunday
Oct | 9 | 3 | 4 | 5 | 6 | 7 | 8 | Dasara - 6th October - Thursday
Oct | 16 | 10 | 11 | 12 | 13 | 14 | 15 |
Oct | 23 | 17 | 18 | 19 | 20 | 21 | 22 |
Oct | 30 | 24 | 25 | 26 | 27 | 28 | 29 | Diwali Amavasya (Laxmi Pujan) - 26th October - Wednessday
Oct | | 31 | | | | | |
Nov | 6 | | 1 | 2 | 3 | 4 | 5 |
Nov | 13 | 7 | 8 | 9 | 10 | 11 | 12 | Bakri Id (iD-Ul-Zun) - 7th November - Monday
Nov | 20 | 14 | 15 | 16 | 17 | 18 | 19 |
Nov | 27 | 21 | 22 | 23 | 24 | 25 | 26 |
Nov | | 28 | 29 | 30 | | | |
Dec | 4 | | | | 1 | 2 | 3 |
Dec | 11 | 5 | 6 | 7 | 8 | 9 | 10 | Moharam - 6th December - Tuesday
Dec | 18 | 12 | 13 | 14 | 15 | 16 | 17 |
Dec | 25 | 19 | 20 | 21 | 22 | 23 | 24 | Christmas - 25th December - Sunday
Dec | | 26 | 27 | 28 | 29 | 30 | 31 |


Best Regards,
Dinesh


On Tue, Sep 4, 2012 at 9:09 PM, 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



--
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.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: crosstab

From
Aram Fingal
Date:
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. 



Re: crosstab

From
Vincent Veyron
Date:
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



Re: crosstab

From
dinesh kumar
Date:
This is really good feature (crostab) to implement .. 

Best Regards,


On Wed, Sep 5, 2012 at 12:21 AM, Vincent Veyron <vv.lists@wanadoo.fr> 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


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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: crosstab

From
Aram Fingal
Date:
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

Re: crosstab

From
Joe Conway
Date:
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




Re: crosstab

From
Aram Fingal
Date:
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

Re: crosstab

From
Joe Conway
Date:
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




Re: crosstab

From
Misa Simic
Date:
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@multifactorial.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: crosstab

From
Aram Fingal
Date:
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

Re: crosstab

From
Aram Fingal
Date:

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 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..

 Okay, you just answered my question in reply to Joe Conway (crossed in the mail).  So I could just use write.table() from R to save to disk or I might even have the PL/R procedure write a heatmap or PCA plot to disk, again without returning any rows to PostgreSQL.

-Aram



Re: crosstab

From
"A.M."
Date:
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





Re: crosstab

From
Misa Simic
Date:
That is correct, 

Re you additional question re PL/R or get data from Pg in plain R - I am not sure it will be that much faster inside PL/R...

I dont think there will be too much difference in execution time... Would be good to test it... and would be good to share results with us :)

Thanks,

Misa



2012/9/4 Aram Fingal <fingal@multifactorial.com>

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 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..

 Okay, you just answered my question in reply to Joe Conway (crossed in the mail).  So I could just use write.table() from R to save to disk or I might even have the PL/R procedure write a heatmap or PCA plot to disk, again without returning any rows to PostgreSQL.

-Aram




Re: crosstab

From
Merlin Moncure
Date:
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


Re: crosstab

From
Joe Conway
Date:
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




Re: crosstab

From
Aram Fingal
Date:

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.

-Aram

Re: crosstab

From
Joe Conway
Date:
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




Re: crosstab

From
Misa Simic
Date:
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)?

If we take this scenario for example (Take some data from DB, pivot them, and save it as CSV)... pseudo code would be:

-Execute SQL query
-pivot returned data
-save it to CSV...

of course everything could be in one function, but I would organize my code in much more... because of for example, if I need tomorrow get the same thing and save it to PDF, or Save it to graph etc... I could copy/paste the same function and change code in step 3... but if something tomorrow should be changed in step 1 or 2 - I would need to make the same change on 3 places... In pure R for example, I would have at least: getPivotedData function and then SaveToCSV would call getPivotedData() and save it in CSV, SaveToPdf would call the same getPivotedData() and save it to PDF... etc, then tomorrow if something should be changed in first two steps - I would change just getPivotedData function... and cover whole 3 scenarios...

Now, of course PostgreSQL function can't return  R object, but plv8 has solved that problem as plv8 Postgres Function returns record - what is an v8 object...

Then inside one plv8 function, we say plv8.find_function("myv8FunctionWhatReturnsv8Object"), call it and takes actuall result and do with that whatever need to be done inside v8 environment...

During I have written this mail - you have replied that actually my question could be acomplished with serialise/deserialise... but I am not sure I would take that approach - because of "methods" of an object would be lost during serialize/deserialize... (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)


Thanks,

Misa




2012/9/4 Joe Conway <mail@joeconway.com>
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




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: crosstab

From
Misa Simic
Date:
(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) 

Just realised I have called wrong method - so above is not true... it returns real v8 object, and methods of that object can be called :)

Sorry,

Misa


2012/9/5 Misa Simic <misa.simic@gmail.com>
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)?

If we take this scenario for example (Take some data from DB, pivot them, and save it as CSV)... pseudo code would be:

-Execute SQL query
-pivot returned data
-save it to CSV...

of course everything could be in one function, but I would organize my code in much more... because of for example, if I need tomorrow get the same thing and save it to PDF, or Save it to graph etc... I could copy/paste the same function and change code in step 3... but if something tomorrow should be changed in step 1 or 2 - I would need to make the same change on 3 places... In pure R for example, I would have at least: getPivotedData function and then SaveToCSV would call getPivotedData() and save it in CSV, SaveToPdf would call the same getPivotedData() and save it to PDF... etc, then tomorrow if something should be changed in first two steps - I would change just getPivotedData function... and cover whole 3 scenarios...

Now, of course PostgreSQL function can't return  R object, but plv8 has solved that problem as plv8 Postgres Function returns record - what is an v8 object...

Then inside one plv8 function, we say plv8.find_function("myv8FunctionWhatReturnsv8Object"), call it and takes actuall result and do with that whatever need to be done inside v8 environment...

During I have written this mail - you have replied that actually my question could be acomplished with serialise/deserialise... but I am not sure I would take that approach - because of "methods" of an object would be lost during serialize/deserialize... (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)


Thanks,

Misa




2012/9/4 Joe Conway <mail@joeconway.com>
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




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: crosstab

From
Joe Conway
Date:
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




Re: crosstab

From
Misa Simic
Date:
Excellent :)

Thanks - looks as acceptable workaround... 

Many thanks,

Misa

2012/9/5 Joe Conway <mail@joeconway.com>
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