Thread: 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.
Hi ,
Crostab Example
=============
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
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
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
This is really good feature (crostab) to implement ..
Best Regards,
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 allsee the documentation for Additional Supplied Modules, in your case
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns
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
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
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>
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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: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
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
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
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 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
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
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:As mentioned somewhere else along this thread, I don't know that it will
> 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.
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
(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,Misa2012/9/4 Joe Conway <mail@joeconway.com>On 09/04/2012 01:23 PM, Aram Fingal wrote:As mentioned somewhere else along this thread, I don't know that it will
> 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.
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
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
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:Mmmm, what about using plr_modules table?
> 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)?
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