Re: Concatenation through SQL - Mailing list pgsql-sql

On Dec 21, 2007 4:16 PM, Leif B. Kristensen <[email protected]> wrote:
> On Friday 21. December 2007, Philippe Lang wrote:
>
> >(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id
> > = appointments.id) AS employees
> >FROM appointments
> >------------------------------
> >
> >... where CONCAT suggest we want to concatenate the variable inside,
> >with the separator ', ' inbetween.
>
> 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've written a Plpgsql function that concatenates the full
> name, but it's big and ugly. 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

Try concatenating your columns using the '||' operator or 'concat' function
and project only one column from the subquery. You won't need to convert it
to an array and back to string.


--Imad
Database Expert