Sorting aggregate column contents

From: Everton Luís Berz <everton(dot)berz(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Sorting aggregate column contents
Date: 2006-05-02 21:00:31
Message-ID: 4457C86F.8040303@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Is it possible to sort the content of an aggregate text column?

Query:
select s.name, ag_concat(c.name) from state s
inner join city c on (c.idstate = s.idstate)
group by s.name
order by s.name;

Result:
name | ag_concat
-------+---------------------------
RS | Porto Alegre, Gramado
SP | Osasco
(2 rows)

Expected result:
name | ag_concat
-------+---------------------------
RS | Gramado, Porto Alegre
SP | Osasco
(2 rows)

I tried "order by s.name, c.name" but it causes a error:
ERROR: column "c.name" must appear in the GROUP BY clause or be used in
an aggregate

My function and aggregate code:
CREATE FUNCTION f_concat (text, text) RETURNS text AS $$
DECLARE
t text;
BEGIN
IF character_length($1) > 0 THEN
t = $1 || ', ' || $2;
ELSE
t = $2;
END IF;
RETURN t;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE ag_concat (
sfunc = f_concat,
basetype = text,
stype = text,
initcond = ''
);

--
Everton

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Volkan YAZICI 2006-05-02 21:13:40 Re: Sorting aggregate column contents
Previous Message Tom Lane 2006-05-02 20:17:57 Re: ERROR: plan should not reference subplan's variable