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
