Re: pg SQL question

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