pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Crosstab
25+ messages / 11 participants
[nested] [flat]

* Crosstab
@ 2007-02-20 23:44 Robert Fitzpatrick <lists@webtent.net>
  0 siblings, 0 replies; 25+ messages in thread

From: Robert Fitzpatrick @ 2007-02-20 23:44 UTC (permalink / raw)
  To: pgsql-general

I am trying to use the crosstab function of the contrib tablefunc.
Reading the README, I believe I am supposed to be using crosstab(sql, N)
for my situation and wondering if the SQL can be based on a view? I have
this view created that gives me each sales rep and their total number of
units sold and total revenue for each month:

CREATE VIEW "public"."view_pick1" (
    rep,
    month,
    units,
    revenue)
AS
SELECT users.user_login AS rep, date_part('month'::text,
    current_clients.start_date) AS "month", count(companies.company_id) AS
    units, sum(companies.company_revenue) AS revenue
FROM ((companies JOIN current_clients ON ((companies.company_id =
    current_clients.client_id))) JOIN users ON ((companies.company_sales_rep =
    users.user_id)))
GROUP BY users.user_login, date_part('month'::text, current_clients.start_date)
ORDER BY users.user_login, date_part('month'::text, current_clients.start_date);

Trying to make a crosstab, let's say just for units, this is what I'm
attempting, which is wrong of course, can someone enlighten me as this
is my first crosstab.

select * from crosstab('select rep, month, units from view_pick1 order by 1,2;', 12) AS view_pick1(rep varchar, jan double precision, feb double precision, mar double precision, apr double precision, may double precision, jun double precision, jul double precision, aug double precision, sep double precision, oct double precision, nov double precision, dec double precision);

Error is: ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601

Not sure what that means, I tried to match up the view field types with
the returned fields. My sql produces the following after which is what I
would like to get. Am I even going about this correctly?

     rep      | month | units
--------------+-------+-------
 aespinal     |     5 |     4
 aespinal     |     6 |     3
 asmith       |     1 |     1
 athranow     |     1 |     5
 athranow     |     2 |     1
 athranow     |     3 |     2
 athranow     |     4 |     1

rep        jan   feb   mar   apr   may   jun   etc...
---------+-----+-----+-----+-----+-----+-----+-
aespinal                            4     3
asmith      1
athranow    5     1     2     1

Thanks for the help!
-- 
Robert




^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* crosstab
@ 2012-09-04 15:39 punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 16:02 ` Re: crosstab dinesh kumar <dineshkumar02@gmail.com>
  2012-09-04 17:15 ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 20:56 ` Re: crosstab Merlin Moncure <mmoncure@gmail.com>
  0 siblings, 4 replies; 25+ messages in thread

From: punnoose @ 2012-09-04 15:39 UTC (permalink / raw)
  To: pgsql-general

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.




^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
@ 2012-09-04 16:02 ` dinesh kumar <dineshkumar02@gmail.com>
  3 siblings, 0 replies; 25+ messages in thread

From: dinesh kumar @ 2012-09-04 16:02 UTC (permalink / raw)
  To: punnoose <punnoose.pj@dwisesolutions.com>; +Cc: pgsql-general

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
manojadinesh.blogspot.com


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
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
@ 2012-09-04 17:15 ` Aram Fingal <fingal@multifactorial.com>
  3 siblings, 0 replies; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 17:15 UTC (permalink / raw)
  To: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
@ 2012-09-04 18:51 ` Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:01   ` Re: crosstab dinesh kumar <dineshkumar02@gmail.com>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  3 siblings, 2 replies; 25+ messages in thread

From: Vincent Veyron @ 2012-09-04 18:51 UTC (permalink / raw)
  To: punnoose <punnoose.pj@dwisesolutions.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
@ 2012-09-04 19:01   ` dinesh kumar <dineshkumar02@gmail.com>
  1 sibling, 0 replies; 25+ messages in thread

From: dinesh kumar @ 2012-09-04 19:01 UTC (permalink / raw)
  To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: punnoose <punnoose.pj@dwisesolutions.com>; pgsql-general

This is really good feature (crostab) to implement ..

Best Regards,
Dinesh
manojadinesh.blogspot.com


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
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
@ 2012-09-04 19:17   ` Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 19:17 UTC (permalink / raw)
  To: 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 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.  Maybe I need to revisit the documentation in case I missed something or if new functionality has been added since I last looked.  

-Aram



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 19:26     ` Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Joe Conway @ 2012-09-04 19:26 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
@ 2012-09-04 19:48       ` Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 19:48 UTC (permalink / raw)
  To: pgsql-general


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



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 19:56         ` Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Joe Conway @ 2012-09-04 19:56 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
@ 2012-09-04 20:23           ` Aram Fingal <fingal@multifactorial.com>
  2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 20:23 UTC (permalink / raw)
  To: 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/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. 

-Aram



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 21:06             ` Joe Conway <mail@joeconway.com>
  2012-09-04 23:45               ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Joe Conway @ 2012-09-04 21:06 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
@ 2012-09-04 23:45               ` Misa Simic <misa.simic@gmail.com>
  2012-09-04 23:49                 ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 23:59                 ` Re: crosstab Joe Conway <mail@joeconway.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Misa Simic @ 2012-09-04 23:45 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Aram Fingal <fingal@multifactorial.com>; pgsql-general

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
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 23:45               ` Re: crosstab Misa Simic <misa.simic@gmail.com>
@ 2012-09-04 23:49                 ` Misa Simic <misa.simic@gmail.com>
  1 sibling, 0 replies; 25+ messages in thread

From: Misa Simic @ 2012-09-04 23:49 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Aram Fingal <fingal@multifactorial.com>; 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,
>
> 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
>>
>
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 23:45               ` Re: crosstab Misa Simic <misa.simic@gmail.com>
@ 2012-09-04 23:59                 ` Joe Conway <mail@joeconway.com>
  2012-09-05 00:21                   ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Joe Conway @ 2012-09-04 23:59 UTC (permalink / raw)
  To: Misa Simic <misa.simic@gmail.com>; +Cc: Aram Fingal <fingal@multifactorial.com>; 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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 23:45               ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 23:59                 ` Re: crosstab Joe Conway <mail@joeconway.com>
@ 2012-09-05 00:21                   ` Misa Simic <misa.simic@gmail.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Misa Simic @ 2012-09-05 00:21 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Aram Fingal <fingal@multifactorial.com>; pgsql-general

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

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 20:18         ` Misa Simic <misa.simic@gmail.com>
  2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Misa Simic @ 2012-09-04 20:18 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
@ 2012-09-04 20:30           ` Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:36             ` Re: crosstab A.M. <agentm@themactionfaction.com>
  2012-09-04 20:51             ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 20:30 UTC (permalink / raw)
  To: pgsql-general


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

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 20:36             ` A.M. <agentm@themactionfaction.com>
  2012-09-04 21:59               ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  1 sibling, 1 reply; 25+ messages in thread

From: A.M. @ 2012-09-04 20:36 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general


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

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

Cheers,
M







^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:36             ` Re: crosstab A.M. <agentm@themactionfaction.com>
@ 2012-09-04 21:59               ` Aram Fingal <fingal@multifactorial.com>
  2012-09-04 23:21                 ` Re: crosstab Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Aram Fingal @ 2012-09-04 21:59 UTC (permalink / raw)
  To: pgsql-general


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=

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:36             ` Re: crosstab A.M. <agentm@themactionfaction.com>
  2012-09-04 21:59               ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 23:21                 ` Joe Conway <mail@joeconway.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Joe Conway @ 2012-09-04 23:21 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
  2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
  2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
  2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
  2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
  2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
@ 2012-09-04 20:51             ` Misa Simic <misa.simic@gmail.com>
  1 sibling, 0 replies; 25+ messages in thread

From: Misa Simic @ 2012-09-04 20:51 UTC (permalink / raw)
  To: Aram Fingal <fingal@multifactorial.com>; +Cc: pgsql-general

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

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
@ 2012-09-04 20:56 ` Merlin Moncure <mmoncure@gmail.com>
  3 siblings, 0 replies; 25+ messages in thread

From: Merlin Moncure @ 2012-09-04 20:56 UTC (permalink / raw)
  To: punnoose <punnoose.pj@dwisesolutions.com>; +Cc: pgsql-general

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




^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* crosstab
@ 2023-03-09 06:07 Rosebrock, Uwe (Environment, Hobart) <Uwe.Rosebrock@csiro.au>
  2023-03-09 13:22 ` Re: crosstab David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Rosebrock, Uwe (Environment, Hobart) @ 2023-03-09 06:07 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi List
I like to use crosstab to pivot a long list of rows into columns, however the column labels are created dynamically with the resulting column type known.

Is there a way pass a list of names and types to crosstab (‘query’) as (<‘type list as strings in array’> ) ?
In other words,
Can I pass an array to crosstab row labels

Cheers
Uwe







^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: crosstab
  2023-03-09 06:07 crosstab Rosebrock, Uwe (Environment, Hobart) <Uwe.Rosebrock@csiro.au>
@ 2023-03-09 13:22 ` David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 25+ messages in thread

From: David G. Johnston @ 2023-03-09 13:22 UTC (permalink / raw)
  To: Rosebrock, Uwe (Environment, Hobart) <Uwe.Rosebrock@csiro.au>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Wednesday, March 8, 2023, Rosebrock, Uwe (Environment, Hobart) <
Uwe.Rosebrock@csiro.au> wrote:

> Hi List
>
> I like to use crosstab to pivot a long list of rows into columns, however
> the column labels are created dynamically with the resulting column type
> known.
>
>
>
> Is there a way pass a list of names and types to crosstab (‘query’) as
> (<‘type list as strings in array’> ) ?
>
> In other words,
>
> Can I pass an array to crosstab row labels
>
>
No, you cannot change query structure at execution time in SQL.

David J.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread


end of thread, other threads:[~2023-03-09 13:22 UTC | newest]

Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-20 23:44 Crosstab Robert Fitzpatrick <lists@webtent.net>
2012-09-04 15:39 crosstab punnoose <punnoose.pj@dwisesolutions.com>
2012-09-04 16:02 ` Re: crosstab dinesh kumar <dineshkumar02@gmail.com>
2012-09-04 17:15 ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 18:51 ` Re: crosstab Vincent Veyron <vv.lists@wanadoo.fr>
2012-09-04 19:01   ` Re: crosstab dinesh kumar <dineshkumar02@gmail.com>
2012-09-04 19:17   ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 19:26     ` Re: crosstab Joe Conway <mail@joeconway.com>
2012-09-04 19:48       ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 19:56         ` Re: crosstab Joe Conway <mail@joeconway.com>
2012-09-04 20:23           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 21:06             ` Re: crosstab Joe Conway <mail@joeconway.com>
2012-09-04 23:45               ` Re: crosstab Misa Simic <misa.simic@gmail.com>
2012-09-04 23:49                 ` Re: crosstab Misa Simic <misa.simic@gmail.com>
2012-09-04 23:59                 ` Re: crosstab Joe Conway <mail@joeconway.com>
2012-09-05 00:21                   ` Re: crosstab Misa Simic <misa.simic@gmail.com>
2012-09-04 20:18         ` Re: crosstab Misa Simic <misa.simic@gmail.com>
2012-09-04 20:30           ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 20:36             ` Re: crosstab A.M. <agentm@themactionfaction.com>
2012-09-04 21:59               ` Re: crosstab Aram Fingal <fingal@multifactorial.com>
2012-09-04 23:21                 ` Re: crosstab Joe Conway <mail@joeconway.com>
2012-09-04 20:51             ` Re: crosstab Misa Simic <misa.simic@gmail.com>
2012-09-04 20:56 ` Re: crosstab Merlin Moncure <mmoncure@gmail.com>
2023-03-09 06:07 crosstab Rosebrock, Uwe (Environment, Hobart) <Uwe.Rosebrock@csiro.au>
2023-03-09 13:22 ` Re: crosstab David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox