X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 3BAD1474E5C
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 13:40:49 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 57749-06 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 13:40:38 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 4A63A475A5C
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 13:24:15 -0500 (EST)
Received: from [66.219.92.2] (HELO chocolate-mousse)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2856789; Tue, 25 Feb 2003 10:24:13 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Jeff Boes <jboes@nexcerpt.com>, pgsql-sql@postgresql.org
Subject: Re: Denormalizing during select
Date: Tue, 25 Feb 2003 10:24:19 -0800
X-Mailer: KMail [version 1.4]
References: <u3kk5v4i276aqv2hv22pn6jh4t3a2ev08g@4ax.com>
	<988d394c6d80ecd1396eac17ff787470@news.teranews.com>
In-Reply-To: <988d394c6d80ecd1396eac17ff787470@news.teranews.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302251024.19249.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/560
X-Sequence-Number: 11958

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.
>=20
> 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 =3D '''' THEN $1
	WHEN $1 IS NULL OR $1 =3D '''' THEN $2
	ELSE $1 || '', '' || $2
	END
' LANGUAGE 'sql';


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

SELECT a.id, a.col1, a.col2, comma_list(b.text)
FROM a JOIN b on a.id =3D 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 th=
e=20
list, but it is *much* faster than the function method that Jeff outlined.


--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco