Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Concatenation through SQL
Date: Fri, 21 Dec 2007 11:33:40 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch>
Thread-Topic: Concatenation through SQL
thread-index: AchDvPR9eYRTOwtZTKeItoqZPJsqEA==
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

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