From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Concatenation through SQL |
Date: | 2007-12-21 10:33:40 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2007-12-21 10:58:13 | Re: Concatenation through SQL |
Previous Message | Richard Huxton | 2007-12-20 08:31:29 | Re: sql query - create replace function |