Received: from fep8.cogeco.net (smtp.cogeco.net [216.221.81.25]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBJJSum26227 for ; Wed, 19 Dec 2001 14:28:57 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from xyzzy.lan.internal (d226-80-178.home.cgocable.net [24.226.80.178]) by fep8.cogeco.net (Postfix) with ESMTP id 3E64C4668; Wed, 19 Dec 2001 14:28:51 -0500 (EST) Received: from localhost ([127.0.0.1] ident=drew) by xyzzy.lan.internal with esmtp (Exim 3.33 #1 (Debian)) id 16GmOY-0002p8-00; Wed, 19 Dec 2001 14:28:50 -0500 Subject: Re: return a text agreggate from a subselect From: "Andrew G. Hammond" To: Louis-David Mitterrand Cc: pgsql-sql@postgresql.org In-Reply-To: <20011219161128.GB28569@apartia.org> References: <20011219161128.GB28569@apartia.org> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-KZobwotpKz6wJTHtTg8m" X-Mailer: Evolution/1.0 (Preview Release) Date: 19 Dec 2001 14:28:49 -0500 Message-Id: <1008790130.855.21.camel@xyzzy> Mime-Version: 1.0 X-Archive-Number: 200112/209 X-Sequence-Number: 6032 --=-KZobwotpKz6wJTHtTg8m Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2001-12-19 at 11:11, Louis-David Mitterrand wrote: > The subselect: (SELECT company_name FROM table)=20 >=20 > The output I'd like: 'Company1|Company2|Company3|etc.' >=20 > Should I write a function or a new agreggate for this or is there is > simpler way? I'm afraid the cleanest way to do this is with an aggregate. CREATE FUNCTION barjoin(text, text) RETURNS text AS ' SELECT CASE WHEN length($1) > 0 THEN $1 || ''|'' || $2 ELSE $2 END;' LANGUAGE 'sql'; CREATE AGGREGATE barconcat(basetype=3Dtext, sfunc=3Dbarjoin,=20 stype=3Dtext, initcond=3D''); SELECT barconcat(company_name) FROM table; If you're in the mood to pointlessly performance tune the snot out of it, you could implement barjoin to simply do $1|$2, and then write a final function for the aggregate that trimmed off the beginning |... --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org=20=20 http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --=-KZobwotpKz6wJTHtTg8m Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iQCVAwUAPCDqcMaBTSA3ULb1AQKAYwQAn+IZI91zpjO6BKSQQD+A7QrjftFO3vus zHnvOIT4m23we22XXXsHzEh00L3dc+Ty6CswUb/ZCqxRv9Cdb8I7hrhBPOAJ/yDD lRI4CYplPtEfd+CnVqppy0JqIlSk+hs2pgRz4ze81B+y0aqknQa7/PopCsCyVS8/ +dTDsxXpIJ0= =/iLv -----END PGP SIGNATURE----- --=-KZobwotpKz6wJTHtTg8m--