From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Louis-David Mitterrand <vindex@apartia.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: return a text agreggate from a subselect
Date: 19 Dec 2001 14:28:49 -0500
Message-ID: <1008790130.855.21.camel@xyzzy> (raw)
In-Reply-To: <20011219161128.GB28569@apartia.org>
References: <20011219161128.GB28569@apartia.org>
On Wed, 2001-12-19 at 11:11, Louis-David Mitterrand wrote:
> The subselect: (SELECT company_name FROM table)
>
> The output I'd like: 'Company1|Company2|Company3|etc.'
>
> 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=text, sfunc=barjoin,
stype=text, initcond='');
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 |...
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: drew@xyzzy.dhs.org, vindex@apartia.org
Subject: Re: return a text agreggate from a subselect
In-Reply-To: <1008790130.855.21.camel@xyzzy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox