Modify postgresql "list" aggregate to remove duplicates

Question

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!

Moved from edit: Unfortunately, Quassnoi's original approach didn't seem to help; I got the same list of results from the new method as I get from what I was using originally. I did try adding WHEN $1 = $2 THEN $1 initially, though I didn't know about SORTOP. — Jenn D.
– Jenn D., Commented Feb 19, 2009 at 17:30

Quassnoi · Accepted Answer · 2009-02-18 22:39:49Z

3

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"

edited Feb 18, 2009 at 22:39

answered Feb 18, 2009 at 21:59

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jenn D. Over a year ago

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!

Quassnoi Over a year ago

Well, the first google hit on "posgresql create aggregate" yields this: postgresql.org/docs/8.1/interactive/sql-createaggregate.html :)

Jenn D. Over a year ago

Oh, definitely; that'll be my first stop. Thanks again!

drewface · Accepted Answer · 2010-11-01 20:21:08Z

3

you can just put a "distinct" qualifier inside the aggregate function call to remove duplicates:

select list(distinct <column>)
from...

answered Nov 1, 2010 at 20:21

drewface

311 bronze badge

Collectives™ on Stack Overflow

Modify postgresql "list" aggregate to remove duplicates

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related