Thread: generic crosstab
I'm using the PostgreSQL crosstab functions to do something like this:
From this table ...
a x 10
b x 13
a y 11
b y 17
... I get:
x y
a 10 11
b 13 17
To get the second table I have to specify the field list when I call the crosstab function (in this example it would be
ASt(x integer, y integer))
My problem arises when that field list changes, for example when someone inserts a record in the first table with a new
value,for example "z". In that case I have to change my queries to AS t(x integer, y integer, z integer)
Do you know how to declare a "generic" field list, or some other library similar to crosstab?
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
I was having the same problem. I defined a "crosstab definition table" where I declare the things I want out of crosstab and then I dynamically build views that will add the needed "AS ct42( .... )". Maybe someone else can suggest a better solution ... Il Tuesday 06 November 2007 13:46:06 Toni Casueps ha scritto: > I'm using the PostgreSQL crosstab functions to do something like this: > > From this table ... > > a x 10 > b x 13 > a y 11 > b y 17 > > > ... I get: > > x y > a 10 11 > b 13 17 > > > To get the second table I have to specify the field list when I call the > crosstab function (in this example it would be AS t(x integer, y integer)) > My problem arises when that field list changes, for example when someone > inserts a record in the first table with a new value, for example "z". In > that case I have to change my queries to AS t(x integer, y integer, z > integer) > > Do you know how to declare a "generic" field list, or some other library > similar to crosstab? > > _________________________________________________________________ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Reg me Please
On 06/11/2007, Toni Casueps <casueps@hotmail.com> wrote: > > I'm using the PostgreSQL crosstab functions to do something like this: > > From this table ... > > a x 10 > b x 13 > a y 11 > b y 17 > > > ... I get: > > x y > a 10 11 > b 13 17 > > > To get the second table I have to specify the field list when I call the crosstab function (in this example it would beAS t(x integer, y integer)) > My problem arises when that field list changes, for example when someone inserts a record in the first table with a newvalue, for example "z". In that case I have to change my queries to AS t(x integer, y integer, z integer) > > Do you know how to declare a "generic" field list, or some other library similar to crosstab? > Hello it isn't possible. PostgreSQL'SQL is strict typed language :(. Pavel Stehule > _________________________________________________________________ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >