On Thu, 27 Jan 2005 10:23:34 -0500, Alexandre Leclerc <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend