Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Sorting aggregate column contents


  • From: Everton Luís Berz <everton(dot)berz(at)gmail(dot)com>
  • To: pgsql-sql(at)postgresql(dot)org
  • Subject: Sorting aggregate column contents
  • Date: Tue, 02 May 2006 18:00:31 -0300
  • Message-id: <4457C86F(dot)8040303(at)gmail(dot)com>

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



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group