Re: Denormalizing during select

Jeff,

> > I have two tables (A and B) in a one-to-many relationship. When
> > retrieving data from one table (A), I need to denormalize the tables
> > during a select so that values of a column in the many table (B) appear
> > as a list in one field of the output.
>
> Well, the straightforward way to do this would be with a function:

Actually, it's much easier to do this using a custom aggregate:

CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1
WHEN $1 IS NULL OR $1 = '''' THEN $2
ELSE $1 || '', '' || $2
END
' LANGUAGE 'sql';

CREATE AGGREGATE comma_list ( BASETYPE = text, SFUNC = comma_cat, STYPE =
text,
INITCOND = '' );

SELECT a.id, a.col1, a.col2, comma_list(b.text)
FROM a JOIN b on a.id = b.a_id
GROUP BY a.id, a.col1, a.col2

The only drawback of this approach is that you cannot order the items in the
list, but it is *much* faster than the function method that Jeff outlined.

--
-Josh Berkus
Aglio Database Solutions
San Francisco