Sorting aggregate column contents

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: ERROR: plan should not reference subplan's variable
Следующее
От: Volkan YAZICI
Дата:
Сообщение: Re: Sorting aggregate column contents