From: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Sorting items in aggregate function
Date: Tue, 12 Sep 2006 16:46:28 +0100
Message-ID: <20060912154628.GB4991@cl.cam.ac.uk> (raw)
I would like to concatenate sorted strings in an aggregate function. I
found a way to do it without sorting[1], but not with.
Here is an example of a setup and what I could like to achieve. Does
anyone have suggestions on what is the best way to get the desired
result?
Thanks,
Steven.
CREATE TABLE a ( -- Names
id INT PRIMARY KEY,
name TEXT NOT NULL);
CREATE TABLE b ( -- Codes
id INT PRIMARY KEY,
code CHAR(2) NOT NULL);
CREATE TABLE ab ( -- m:n relationship between a and b
id SERIAL PRIMARY KEY,
a_id INT NOT NULL,
b_id INT NOT NULL);
COPY a(id,name) FROM STDIN DELIMITER '|';
1|Alice
2|Bob
3|Charlie
\.
COPY b(id, code) FROM STDIN DELIMITER '|';
1|a
2|b
3|c
4|d
\.
COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
2|4
2|1
3|2
3|3
\.
-- Custom aggregate function which concatenates strings
CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '',
);
-- Current query
SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
FROM
a LEFT JOIN ab ON (a.id=ab.a_id)
LEFT JOIN b ON (ab.b_id=b.id)
GROUP BY a.name
ORDER BY codes;
-- Actual output:
--
-- name | codes
-- ---------+-------
-- Alice |
-- Charlie | b c
-- Bob | d a
-- Desired output:
--
-- name | codes
-- ---------+--------
-- Alice |
-- Bob | a d
-- Charlie | b c
[1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
--
w: http://www.cl.cam.ac.uk/users/sjm217/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: psql+Steven.Murdoch@cl.cam.ac.uk
Subject: Re: Sorting items in aggregate function
In-Reply-To: <20060912154628.GB4991@cl.cam.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox