Sorting aggregate column contents
От | Everton Luís Berz |
---|---|
Тема | Sorting aggregate column contents |
Дата | |
Msg-id | 4457C86F.8040303@gmail.com обсуждение исходный текст |
Ответы |
Re: Sorting aggregate column contents
|
Список | 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
В списке pgsql-sql по дате отправления: