concatenate text

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.

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

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!

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

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.