Date: Tue, 12 Sep 2006 16:46:28 +0100
From: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Sorting items in aggregate function
Message-ID: <20060912154628.GB4991@cl.cam.ac.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i

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/