Re: SELECT query experts, anyone? - Mailing list pgsql-general

From Mark Roberts
Subject Re: SELECT query experts, anyone?
Date
Msg-id 1219255345.14010.77.camel@localhost
Whole thread Raw
In response to SELECT query experts, anyone?  ("Teemu Juntunen" <teemu.juntunen@e-ngine.fi>)
List pgsql-general
If you put this in the application, you could do something such as:

my @manufacturers = fetch("select manufacturer_no, name from
manufacturers");
my @select_fields = ('product_no');
foreach my $manufacturer (@manufacturers) {
    my $manuf_no = $manufacturer->{manufacturer_no};
    my $name = $manufacturer->{name};
    push(@select_fields, "case when x.manufacturer_no = $manuf_no then
'$name' else null end as manuf_${manuf_no}_products);
}

my @outer_select_fields = ('product_no', map { my $manuf = "manuf_" .
$_->{manufacturer_no} . "_products";  "sum($manuf) as $manuf" }
@manufacturers);

my @dataset = fetch("
select @{[ join(",\n", @outer_select_fields) ]}
from (
    select @{[ join(",\n", @select_fields) ]}
    from products_by_manufacturer x
    ) x
group by product_no

Uh, or something like that.  Perl in Evolution is really.. painful.

-Mark

On Wed, 2008-08-20 at 15:50 +0300, Teemu Juntunen wrote:
> Hi Experts,
>
> is threre any way to SELECT values in columns instead of rows? For
> example select products and their manufacters in the followin way:
>
> product1; manufacturer1; manufacturer2;,,, manufacturerN
> product2; manufacturer3;
> product3; manufacturer1;.. manufacturerN-1
>
> With a function you could put the product manufacturers in one string,
> but I would like to have them in columns.
>
> How about arrays. Is there a way to SELECT values in an array to
> columns?
>
> Best regards and thanks,
> Teemu Juntunen


pgsql-general by date:

Previous
From: "Blakely, Jerel (Mission Systems)"
Date:
Subject: Re: Silent install 8.3 diiffers from 8.2
Next
From: James Neff
Date:
Subject: function SETOF return type with variable columns?