Re: Concatenation through SQL

Hi,

Here is small reduced test database:

------------------------------
CREATE TABLE appointments
(
id integer,
name varchar(32),
CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
(
id integer,
appointment_id integer,
name varchar(32),
CONSTRAINT employees_pkey PRIMARY KEY (id),
CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');
------------------------------

I'm trying to write an SQL query that would return this:

---------------------------------------------------
appointment count_employees employees
---------------------------------------------------
app1 3 emp1, emp2, emp3
app2 2 emp1, emp4,
---------------------------------------------------

First part is easy to write:

------------------------------
SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
FROM appointments
------------------------------

... But concatenating employees name is harder, at least for me...

I'm convinced this can be done with Set Returning Functions and a bit of
plpgsql, but I was wondering if it would possible to write some kind of
extension to Postgresql (operator?) that would allow this kind of
syntax:

------------------------------
SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
(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.

Thanks for your tips!

Philippe Lang

Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> schrieb:

2 ways:

* You can use something like this:

test=*# select * from a;
id | val
----+-----
1 | foo
2 | bar
(2 rows)

test=*# select array_to_string(array(select val from a), ', ');
array_to_string
-----------------
foo, bar
(1 row)

* comma-aggregate, see
http://www.zigo.dhs.org/postgresql/#comma_aggregate

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Hello

postgres=# select name, count, employes from (select appointment_id,
count(*), (SELECT array_to_string(ARRAY(SELECT name from employees
where appointment_id = e.appointment_id),',')) as employes from
employees e group by appointment_id)s join appointments a on a.id =
s.appointment_id;
name | count | employes
------+-------+----------------
app2 | 2 | emp1,emp4
app1 | 3 | emp1,emp2,emp3
(2 rows)

regards
Pavel Stehule

for longer table is better define own aggregate function.

On 21/12/2007, Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> wrote:
> Hi,
>
> Here is small reduced test database:
>
> ------------------------------
> CREATE TABLE appointments
> (
> id integer,
> name varchar(32),
> CONSTRAINT appointments_pkey PRIMARY KEY (id)
> );
>
> CREATE TABLE employees
> (
> id integer,
> appointment_id integer,
> name varchar(32),
> CONSTRAINT employees_pkey PRIMARY KEY (id),
> CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
> REFERENCES appointments (id)
> );
>
> INSERT INTO appointments VALUES (1, 'app1');
> INSERT INTO employees VALUES (1, 1, 'emp1');
> INSERT INTO employees VALUES (2, 1, 'emp2');
> INSERT INTO employees VALUES (3, 1, 'emp3');
>
> INSERT INTO appointments VALUES (2, 'app2');
> INSERT INTO employees VALUES (4, 2, 'emp1');
> INSERT INTO employees VALUES (5, 2, 'emp4');
> ------------------------------
>
>
> I'm trying to write an SQL query that would return this:
>
> ---------------------------------------------------
> appointment count_employees employees
> ---------------------------------------------------
> app1 3 emp1, emp2, emp3
> app2 2 emp1, emp4,
> ---------------------------------------------------
>
> First part is easy to write:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> FROM appointments
> ------------------------------
>
> ... But concatenating employees name is harder, at least for me...
>
> I'm convinced this can be done with Set Returning Functions and a bit of
> plpgsql, but I was wondering if it would possible to write some kind of
> extension to Postgresql (operator?) that would allow this kind of
> syntax:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> (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.
>
> Thanks for your tips!
>
>
> Philippe Lang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

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

Is there any way to accomplish this from Plpgsql?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

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

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.

On Friday 21. December 2007, Niklas Johansson wrote:

>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.

Aah, great!

pgslekt=> select array_to_string(array[given, patronym, toponym], ' ')
from persons where person_id=57;
array_to_string
--------------------------
Abraham Jonsen Bjørntvet
(1 row)

Tackar och bockar!
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

pgsql-sql-owner(at)postgresql(dot)org wrote:
> Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> schrieb:
>
> 2 ways:
>
> * You can use something like this:
>
> test=*# select * from a;
> id | val
> ----+-----
> 1 | foo
> 2 | bar
> (2 rows)
>
> test=*# select array_to_string(array(select val from a), ', ');
> array_to_string -----------------
> foo, bar
> (1 row)
>
>
> * comma-aggregate, see
> http://www.zigo.dhs.org/postgresql/#comma_aggregate

Hi,

Thanks to all who responded, in particular Andreas. I'm always amazed by
the quality of both the Postgresql database and the support in its
newsgroups.

The "comma-aggregate" worked just fine for me. Here is the final
example, for those willing to test it. This will be more than useful!

---------------------------------------
CREATE TABLE appointments
(
id integer,
name varchar(32),
CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
(
id integer,
appointment_id integer,
name varchar(32),
CONSTRAINT employees_pkey PRIMARY KEY (id),
CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees,
(SELECT array_to_string(array_accum(name),', ') FROM employees AS e
WHERE e.appointment_id = appointments.id) AS employees
FROM appointments
---------------------------------------

Result:

---------------------------------------------------
appointment count_employees employees
---------------------------------------------------
app1 3 emp1, emp2, emp3
app2 2 emp1, emp4,
---------------------------------------------------

Merry christmas to all.

Philippe

On 21/12/2007, Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> wrote:
> pgsql-sql-owner(at)postgresql(dot)org wrote:
> > Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> schrieb:
> >
> > 2 ways:
> >
> > * You can use something like this:
> >
> > test=*# select * from a;
> > id | val
> > ----+-----
> > 1 | foo
> > 2 | bar
> > (2 rows)
> >
> > test=*# select array_to_string(array(select val from a), ', ');
> > array_to_string -----------------
> > foo, bar
> > (1 row)
> >
> >
> > * comma-aggregate, see
> > http://www.zigo.dhs.org/postgresql/#comma_aggregate
>
> Hi,
>
> Thanks to all who responded, in particular Andreas. I'm always amazed by
> the quality of both the Postgresql database and the support in its
> newsgroups.
>
> The "comma-aggregate" worked just fine for me. Here is the final
> example, for those willing to test it. This will be more than useful!
>
> ---------------------------------------
> CREATE TABLE appointments
> (
> id integer,
> name varchar(32),
> CONSTRAINT appointments_pkey PRIMARY KEY (id)
> );
>
> CREATE TABLE employees
> (
> id integer,
> appointment_id integer,
> name varchar(32),
> CONSTRAINT employees_pkey PRIMARY KEY (id),
> CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
> REFERENCES appointments (id)
> );
>
> INSERT INTO appointments VALUES (1, 'app1');
> INSERT INTO employees VALUES (1, 1, 'emp1');
> INSERT INTO employees VALUES (2, 1, 'emp2');
> INSERT INTO employees VALUES (3, 1, 'emp3');
>
> INSERT INTO appointments VALUES (2, 'app2');
> INSERT INTO employees VALUES (4, 2, 'emp1');
> INSERT INTO employees VALUES (5, 2, 'emp4');
>
> CREATE AGGREGATE array_accum (anyelement)
> (
> sfunc = array_append,
> stype = anyarray,
> initcond = '{}'
> );
>
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees,
> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
> WHERE e.appointment_id = appointments.id) AS employees
> FROM appointments
> ---------------------------------------
>

hello,

one note: you can merge your two subselect into one. It's unimportant
on 100 rows, but it's can be important on thousands rows.

nice a day
Pavel

>
> Result:
>
> ---------------------------------------------------
> appointment count_employees employees
> ---------------------------------------------------
> app1 3 emp1, emp2, emp3
> app2 2 emp1, emp4,
> ---------------------------------------------------
>
>
> Merry christmas to all.
>
> Philippe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Pavel Stehule wrote:

>> SELECT
>> appointments.name AS appointment,
>> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
>> appointments.id) AS num_employees,
>> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
>> WHERE e.appointment_id = appointments.id) AS employees FROM
>> appointments ---------------------------------------
>>
>
> hello,
>
> one note: you can merge your two subselect into one. It's unimportant
> on 100 rows, but it's can be important on thousands rows.
>
> nice a day
> Pavel

Hi Pavel,

Since subselects must return one single column, is that what you are
talking about? I have just modified the code you have posted before.

---------------------------
SELECT
name AS appointment,
num_employees,
employees

FROM
(
SELECT
appointment_id,
COUNT(*) AS num_employees,
(SELECT array_to_string(array_accum(name),', ') FROM employees WHERE
e.appointment_id = appointment_id) AS employees
FROM employees e
GROUP BY appointment_id
)s

JOIN appointments a on a.id = s.appointment_id;
---------------------------

Philippe