Thread: Concatenating several rows

SELECT name FROM pseudonyms WHERE person_id=125;

I know in advance that this query yields between 0 and 5 rows of
results, depending on the actual person_id.

How can I concatenate those results into one text string?  Something like:

SELECT array_to_string( (SELECT name FROM pseudonyms WHERE person_id=125), ' ');

which doesn't work...

make it an array first:

SELECT array_to_string(array(SELECT name FROM pseudonyms WHERE
person_id=125), ' ');


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of
> Pierre Thibaudeau
> Sent: Monday, January 29, 2007 6:50 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Concatenating several rows
>
> SELECT name FROM pseudonyms WHERE person_id=125;
>
> I know in advance that this query yields between 0 and 5 rows of
> results, depending on the actual person_id.
>
> How can I concatenate those results into one text string?
> Something like:
>
> SELECT array_to_string( (SELECT name FROM pseudonyms WHERE
> person_id=125), ' ');
>
> which doesn't work...
>

Thank you, George and Phillip!  I was trying something similar (doing
a cast) which wasn't working:

SELECT array_to_string( (SELECT name FROM pseudonyms WHERE
person_id=125)::array, ' ');


In the meantime, I wrote a little function to do the job, but your
solution is simpler:

CREATE OR REPLACE FUNCTION persons.aggregatenames(personid integer)
  RETURNS text AS
$BODY$declare
somerow record DEFAULT '';
thenames text;
BEGIN
FOR somerow IN SELECT name FROM pseudonyms WHERE person_id=personid LOOP
    thenames := thenames || ' ' || somerow.name ;
END LOOP;
RETURN thenames;
END;$BODY$
  LANGUAGE 'plpgsql' STABLE;



> SELECT array_to_string(array(SELECT name FROM pseudonyms WHERE
> person_id=125), ' ');