Postgres Pro
Downloads
Home   >   mailing lists

Thread: concatenate text

concatenate text

From
Sterpu Victor
Date:
Can I do something like this?
SELECT sum(name) FROM table;

Where name is a text field.
I know 'sum' doesn't work, but is there another solution?
'||' is not good because it will take just 2 arguments.

Thank you.

Re: concatenate text

From
David Fetter
Date:
On Mon, Sep 05, 2005 at 03:00:30AM +0300, Sterpu Victor wrote:
> Can I do something like this?
> SELECT sum(name) FROM table;
>
> Where name is a text field.
> I know 'sum' doesn't work, but is there another solution?
> '||' is not good because it will take just 2 arguments.

Currently, you can use a custom aggregate like array_accum()
http://www.postgresql.org/docs/current/static/xaggr.html

combined with the array_to_string() like this:

SELECT array_to_string(array_accum(email), ', ') AS "emails"
FROM person_email;

Hope this helps :)

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

Remember to vote!

Re: concatenate text

From
Michael Fuhr
Date:
On Mon, Sep 05, 2005 at 03:00:30AM +0300, Sterpu Victor wrote:
> Can I do something like this?
> SELECT sum(name) FROM table;
>
> Where name is a text field.
> I know 'sum' doesn't work, but is there another solution?
> '||' is not good because it will take just 2 arguments.

Are you looking for an aggregate in particular or will any solution
suffice?  Here's a trivial example that works in 7.4 and later:

CREATE TABLE foo (name text);

INSERT INTO foo VALUES ('Alice');
INSERT INTO foo VALUES ('Bob');
INSERT INTO foo VALUES ('Carol');
INSERT INTO foo VALUES ('Dave');

SELECT array_to_string(ARRAY(SELECT name FROM foo), '');
  array_to_string
-------------------
 AliceBobCarolDave
(1 row)

If you need an aggregate then search the archives; examples have
been posted before.

--
Michael Fuhr

Re: concatenate text

From
Allan Wang
Date:
On Sun, 2005-09-04 at 20:37 -0400, Allan Wang wrote:
> On Mon, 2005-09-05 at 03:00 +0300, Sterpu Victor wrote:
> > Can I do something like this?
> > SELECT sum(name) FROM table;
> >
>
> You can add a custom aggregate function that turns your data into an
> array, see http://www.postgresql.org/docs/current/static/xaggr.html
>
> You can then use array_to_string if you want them comma seperated or
> something.
>
> Allan Wang

Whoops, I keep forgetting to hit reply to all.

Re: concatenate text

From
Tino Wildenhain
Date:
Am Montag, den 05.09.2005, 03:00 +0300 schrieb Sterpu Victor:
> Can I do something like this?
> SELECT sum(name) FROM table;
>
> Where name is a text field.
> I know 'sum' doesn't work, but is there another solution?
> '||' is not good because it will take just 2 arguments.
>

Yes you can write an aggregate using built in textcat()
function or via array_append() if you are bulding an
array first and translate it into a string on output
of your aggregate. (This should be faster, I personally
used the version with textcat)

Regards
Tino

Re: concatenate text

From
Berend Tober
Date:
David Fetter wrote:

>On Mon, Sep 05, 2005 at 03:00:30AM +0300, Sterpu Victor wrote:
>
>
>>Can I do something like this?
>>SELECT sum(name) FROM table;
>>
>>Where name is a text field.
>>I know 'sum' doesn't work, but is there another solution?
>>'||' is not good because it will take just 2 arguments.
>>
>>
>
>Currently, you can use a custom aggregate like array_accum()
>http://www.postgresql.org/docs/current/static/xaggr.html
>
>combined with the array_to_string() like this:
>
>SELECT array_to_string(array_accum(email), ', ') AS "emails"
>FROM person_email;
>
>
>

Check out the "User Comments" at

http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html

There are a couple good suggestions there that don't deal with arrays.