From: Everton Luís Berz <everton.berz@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Sorting aggregate column contents
Date: Tue, 02 May 2006 18:00:31 -0300
Message-ID: <4457C86F.8040303@gmail.com> (raw)
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
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: everton.berz@gmail.com
Subject: Re: Sorting aggregate column contents
In-Reply-To: <4457C86F.8040303@gmail.com>
* 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