Thread: pg SQL question

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?

For example, suppose I have table foo (key integer, id integer, entry
varchar) with data

    key    id    entry
    1    1    "Four score and seven years ago our fathers "
    1    2    "brought forth on this continent, a new nation, "
    1    3    "conceived in Liberty, and dedicated to the "
    1    4    "proposition that all men are created equal."

and I want to produce the following result:

"Four score and seven years ago our fathers brought forth on this continent,
a new nation, conceived in Liberty, and dedicated to the proposition that
all men are created equal."

I know this could be done writing a plpgsql function, but it seems so basic,
I thought there might be something I'm overlooking.

TIA.

Ed

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

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?
>
> For example, suppose I have table foo (key integer, id integer,
> entry varchar) with data
>
>     key    id    entry
>     1    1    "Four score and seven years ago our fathers "
>     1    2    "brought forth on this continent, a new nation, "
>     1    3    "conceived in Liberty, and dedicated to the "
>     1    4    "proposition that all men are created equal."
>
> and I want to produce the following result:
>
> "Four score and seven years ago our fathers brought forth on this
> continent, a new nation, conceived in Liberty, and dedicated to the
> proposition that all men are created equal."

SELECT f.key, array_to_string(ARRAY(
    SELECT entry
    FROM  foo
    ORDER BY id
    WHERE key = f.key
), '') AS "blurb"
FROM foo f;

> I know this could be done writing a plpgsql function, but it seems
> so basic, I thought there might be something I'm overlooking.

Well, it's not *totally* basic, and it draws on a few different
things, but you can do it with builtins.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!