Re: pgsql crosstab function

Fabrizio Mazzoni wrote:
> Hello all .. i just wrote this script in plpgsql. Basically it
> performs a crosstab query. The difference between this and the one
> you already have in contrib is that this one has no limitations on
> the arguments passed to it.

I don't understand what you think is different. Your example can be
produced using contrib/tablefunc/crosstab like so:

regression=# select art, coalesce(s,0) as "S", coalesce(m,0) as "M",
coalesce(l,0) as "L", coalesce(xl,0) as "XL", coalesce(xxl,0) as "XXL"
from crosstab('select art, tgl, sum(qty) from art group by art, tgl
order by 1', 'select ''L'' union all select ''M'' union all select ''S''
union all select ''XL'' union all select ''XXL''') as (art varchar, L
int, M int, S int, XL int, XXL int);
art | S | M | L | XL | XXL
----------+----+----+----+----+-----
508301 | 0 | 0 | 10 | 38 | 0
5001001 | 0 | 12 | 0 | 0 | 25
45370104 | 10 | 0 | 0 | 0 | 0
(3 rows)

Or with nulls in place of zeros, like this:

regression=# select * from crosstab('select art, tgl, sum(qty) from art
group by art, tgl order by 1', 'select ''S'' union all select ''M''
union all select ''L'' union all select ''XL'' union all select
''XXL''') as (art varchar, L int, M int, S int, XL int, XXL int);

art | l | m | s | xl | xxl
----------+----+----+----+----+-----
508301 | | | 10 | 38 |
5001001 | | 12 | | | 25
45370104 | 10 | | | |
(3 rows)

Joe