Re: Concatenation through SQL

On Dec 21, 2007 4:16 PM, Leif B. Kristensen <leif(at)solumslekt(dot)org> 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