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

Re: order function in aggregate

From: Mike Toews <mwtoews(at)sfu(dot)ca>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: order function in aggregate
Date: 2008-08-20 19:47:14
Message-ID: 48AC74C2.8060400@sfu.ca (view raw or flat)
Thread:
Lists: pgsql-sql
Richard Huxton wrote:
> Michael Toews wrote:
>
> You could accumulate the values in an array and then sort that with 
> the final-func that create aggregate supports.

Thanks for the help. Here was my final functions to aggregate things 
into a comma serpented text element (if anyone is interested):

CREATE OR REPLACE FUNCTION commacat_fn(anyarray)
  RETURNS text AS
$BODY$select array_to_string(sort($1),', ');$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT
  COST 100;
ALTER FUNCTION commacat_fn(anyarray) OWNER TO postgres;

CREATE AGGREGATE commacat(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=commacat_fn,
  INITCOND='{}'
);

---------------

Lastly a random quick example:

select attrelid, commacat(attname) as attnames from pg_attribute group 
by attrelid order by attrelid;

Certainly there are far better examples that can be used to distill 
information in a table to a comma-separated list.

In some specific applications, a "sort(myarraytype[])" function will 
need to be created if the data type in the aggregate column does not 
have a sort function (fortunately, most data types already have this 
function defined).

Regards,
+mt

In response to

pgsql-sql by date

Next:From: Shashwat_NigamDate: 2008-08-21 08:53:51
Subject: Re: Regarding access to a user
Previous:From: EXT-Rothermel, Peter MDate: 2008-08-20 17:16:40
Subject: looking for example of inserting into a view

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group