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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
