2011/12/15 Robert James <srobertjames@gmail.com>:
> To match the heavily denormalized format of a legacy app, I need to
> take a query which gives this:
>
> name | product | rent | own
> Bob | Car | true | false
> Bob | Car | false | true
> Bob | Bike | false | true
> Bob | Truck | true | true
>
> and denormalize it into this:
>
> name | rented_products | owned_products
> Bob | {Car, Truck} | {Car, Truck, Bike}
>
> I thought I could do this using array_agg, but I don't see how to do
> that on a condition. In pseudocode, I'd like to do this:
> SELECT
> uniq(array_agg(product WHERE rent)) AS rented_products,
> uniq(array_agg(product WHERE own)) AS owned_products
> ...
CREATE OR REPLACE array_uniq(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;
SELECT array_uniq(array_agg(CASE WHEN rent THEN product ELSE NULL
END)) AS rented_product,
...
Regards
Pavel Stehule
>
> How can I achieve this? (I'm using Postgres 8.3)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general