Re: Concatenation through SQL

On 21 dec 2007, at 12.16, Leif B. Kristensen wrote:
> I've got a similar problem. My persons table has a number of fields
> for
> different name parts: given, patronym, toponym, surname, occupation,
> epithet.

> I'd like something more elegant, like the
> Python or PHP join() function. I tried Andreas' suggestion like this:
>
> pgslekt=> select array_to_string(array(select given, patronym, toponym
> from persons where person_id=57), ' ');
> ERROR: subquery must return only one column

Your case is not the same as Philippe's, since you have the values to
be concatenated in columns, whereas he had them in rows.

However, something like this would probably achieve what you're
looking for:

select array_to_string(array[given, patronym, toponym], ' ') from
persons where person_id=57;

Notice the use of the array[] constructor, instead of the array()
constructor, which must be fed a subquery which returns only one column.