DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding;
	b=DKs8cd0kPoiKvbjRwKVKViK3SvAiXeDGpnBUFq1HS+/IB8aSXeEuETCYlLpnloZ9/nL6Y9D7WFNWzlRABCe4vzZALjIl2zz7g9sMGlUnq4SJT1olTtJlns0YRT3LnK38Ys2hLRZABNJlerx31xfE3mb7sAFC4gFNWLI8cy15Xy8=
Message-ID: <4457C86F.8040303@gmail.com>
Date: Tue, 02 May 2006 18:00:31 -0300
From: =?ISO-8859-1?Q?Everton_Lu=EDs_Berz?= <everton.berz@gmail.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Sorting aggregate column contents
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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