Re: Flattening a kind of 'dynamic' table

On Thu, 27 Jan 2005 10:23:34 -0500, Alexandre Leclerc
<alexandre(dot)leclerc(at)gmail(dot)com> wrote:
> Here a normal listing of design.product_department_time:
> product_id | department_id | req_time
> ------------+---------------+----------
> 906 | A | 3000
> 906 | C | 3000
> 906 | D | 1935
> 907 | A | 1500
> 907 | C | 1500
> 907 | D | 4575
> 924 | A | 6000
> 924 | C | 1575

Well, I did something like this recently; it can be done though
maybe not very efficiently...

Unfortunately we will need a rowtype with all the departaments:
CREATE DOMAIN departaments AS (a int, b int, c int, d int, ...);

A function aggregate for this type:
CREATE FUNCTION dep_agg(ds departaments, args text[]) RETURNS departaments AS $$
BEGIN
IF args[1] = 'A' THEN ds.a = args[2]; -- I think it is not
possible to do ds.$args[1] = args[2] equivalent.
ELSIF args[1] = 'B' THEN ds.b = args[2];
ELSIF args[1] = 'C' THEN ds.c = args[2];
ELSIF args[1] = 'D' THEN ds.d = args[2];
END IF;
RETURN ds;
END;
$$ LANUGAGE plpgsql;

THEN an aggregate:
CREATE AGGREGATE dep_aggregate (basetype = text[], stype =
departaments, sfunc =dep_agg);

AND then a view for sugar:

CREATE VIEW prod_dep_time VIEW AS
SELECT product_id, (dep_aggregate(ARRAY[departament_id, req_time]::text[])).*
FROM product_department_time GROUP BY product_id;

And voila. :)
Couple of comments:
-- aggregate takes array[] since making "multicolumn" aggregates is
not possible, as far as I know.
-- I did not check the code, yet I did manage to make it work some time before.
You may need to use "ROWS" or something in the function definition; I
don't remember and can't check it right now.
-- comments welcome. :)

Regards,
Dawid