Re: return a text agreggate from a subselect - Mailing list pgsql-sql

From Andrew G. Hammond
Subject Re: return a text agreggate from a subselect
Date
Msg-id 1008790130.855.21.camel@xyzzy
Whole thread Raw
In response to return a text agreggate from a subselect  (Louis-David Mitterrand <vindex@apartia.org>)
Responses Connections?
should temporary tables show up in \dt as type "temporary" or as something?
List pgsql-sql
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

pgsql-sql by date:

Previous
From: "David M. Richter"
Date:
Subject: queryoptimizer force index use
Next
From: "Peter T. Brown"
Date:
Subject: controlling process priority