From: | Richard Poole <rp(at)guests(dot)deus(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
Subject: | Re: pg SQL question |
Date: | 2005-01-22 21:36:01 |
Message-ID: | 20050122213601.GB4160@guests.deus.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Jan 22, 2005 at 02:03:58PM -0700, Ed L. wrote:
>
> There's probably an obvious answer for this, but I couldn't see it in the
> docs. What's the simplest way to concatenate multiple same-column values
> in SQL?
You can create an aggregate that does nothing but concatenate the entries:
CREATE AGGREGATE concat (
BASETYPE = TEXT,
SFUNC = textcat,
STYPE = TEXT,
INITCOND = ''
);
This uses the "textcat" function, which is already lurking in Postgres to
implement the || operator. Then you can go:
SELECT concat(entry) FROM (
SELECT * FROM speech ORDER BY id
) AS lines;
And it will do what you want. The subselect with the ORDER BY guarantees
that the lines come out in the order you put them in.
Richard
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Bernat | 2005-01-22 21:42:34 | dbf2pg error |
Previous Message | Tom Lane | 2005-01-22 21:29:43 | Re: SCHEMA compatibility with Oracle/DB2/Firebird |