views:

752

answers:

2

One of my favorite postgres aggregates is "list", attributed to "Chris Rohlfs in the idocs" according to the scanty evidence I can find on the web.

CREATE FUNCTION comma_cat (text, text)
  RETURNS text AS
  'SELECT CASE
    WHEN $2 is null or $2 = '''' THEN $1
    WHEN $1 is null or $1 = '''' THEN $2
    ELSE $1 || '', '' || $2
  END'
LANGUAGE sql;

CREATE AGGREGATE list (BASETYPE = text, SFUNC = comma_cat, STYPE = text, INITCOND = '');

I find, sometimes, that I would like it to eliminate duplicates. An old mailing list thread suggests that this approach can't do sorting, which might be a deal-killer for duplication removal. Another post in the same thread suggests a function that does the same thing; maybe it's more modifiable for this purpose?

In the meantime, I'll just massage the output in another language. But it would be cool if we could do this directly in postgres!

+2  A: 

You can use intermediate arrays:

CREATE OR REPLACE FUNCTION comma_cat(text[], text)
  RETURNS text[] AS
$BODY$
  SELECT
    CASE WHEN $1 @> ARRAY[$2] THEN $1
    ELSE $1 || $2
  END
$BODY$
  LANGUAGE 'sql' VOLATILE;

CREATE OR REPLACE FUNCTION comma_finish(text[])
  RETURNS text AS
$BODY$
    SELECT array_to_string($1, ', ')
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;

CREATE AGGREGATE list (BASETYPE = text, SFUNC = comma_cat, STYPE = text[], FINALFUNC = comma_finish, INITCOND = '{NULL, NULL}');

value    id
-------- --
"puer"   1
"socer"  2
"vesper" 3
"vesper" 4
"gener"  5
"asper"  6
"asper"  7
"miser"  8
"tener"  9
"liber"  10
"puer"   11

SELECT list(value)
FROM t_text

"puer, vesper, gener, asper, miser, tener, liber, socer"
Quassnoi
Ah ha; now this one works, though I had to remove the COST 100 because it gave me a syntax error (postgresql 8.2.3). Thanks a ton. Now I need to go find a good reference on creating functions and aggregates so I can understand what it's doing!
Jenn D.
Well, the first google hit on "posgresql create aggregate" yields this: http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html :)
Quassnoi
Oh, definitely; that'll be my first stop. Thanks again!
Jenn D.