pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
return a text agreggate from a subselect
2+ messages / 2 participants
[nested] [flat]

* return a text agreggate from a subselect
@ 2001-12-19 16:11  Louis-David Mitterrand <vindex@apartia.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Louis-David Mitterrand @ 2001-12-19 16:11 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I am trying to convert a subselect that has several matches into one
string comprising all matches concatenated:

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?

Thanks in advance,

-- 
    PANOPE: On dit même qu'au trône une brigue insolente
            Veut placer Aricie et le sang de Pallante.
                                          (Phèdre, J-B Racine, acte 1, scène 4)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: return a text agreggate from a subselect
@ 2001-12-19 19:28  Andrew G. Hammond <drew@xyzzy.dhs.org>
  parent: Louis-David Mitterrand <vindex@apartia.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Andrew G. Hammond @ 2001-12-19 19:28 UTC (permalink / raw)
  To: Louis-David Mitterrand <vindex@apartia.org>; +Cc: 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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-12-19 19:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-19 16:11 return a text agreggate from a subselect Louis-David Mitterrand <vindex@apartia.org>
2001-12-19 19:28 ` Andrew G. Hammond <drew@xyzzy.dhs.org>

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