postgresql create aggregate with order by and default parameter

Question

In Postgres 9.x I can do like this

select dept_id, string_agg(user_id, ':' order by user_id) 
from dept_user 
group by dept_id;
+---------+------------+
| dept_id | string_agg |
+---------+------------+
| d1      | u1:u2:u3   |
| d2      | u3:u4      |
+---------+------------+

But my company uses Postgres 8.3, so I find a aggregate function can do like string_agg

create schema WMSYS;
create or replace function WMSYS.sf_concat(text,text) returns text as $$
  select case when coalesce($1, '') <> '' then $1||','||$2 else $2 end;
$$ language sql called on null input;
create aggregate WMSYS.wm_concat (text) (sfunc=WMSYS.sf_concat,stype=text);

the result is:

select dept_id, WMSYS.wm_concat(user_id) 
from dept_user
group by dept_id;
+---------+-----------+
| dept_id | wm_concat |
+---------+-----------+
| d1      | u3,u1,u2  |
| d2      | u3,u4     |
+---------+-----------+

But the result is not sorted(u3,u1,u2 should be u1,u2,u3) and join string(,) is not a parameter. I want usage like this:

WMSYS.wm_concat(user_id)            ## join by ',' and don't sort
WMSYS.wm_concat(user_id, ':')       ## join by ':' and don't sort
WMSYS.wm_concat(user_id, ':', true) ## join by ':' and order by user_id

how to do that?

stackoverflow.com/a/2561297/330315 and stackoverflow.com/q/16455483/330315 — user330315
– user330315, Commented Sep 18, 2017 at 7:55

Vao Tsun · Accepted Answer · 2017-09-18 07:45:42Z

try this:

preparing data sample:

t=# create table a1 (i int, t text);
CREATE TABLE
t=# insert into a1 select 1,'u'||g from generate_series(1,9,1) g;
INSERT 0 9
t=# update a1 set i =2 where ctid > '(0,4)';
UPDATE 5
t=# select i,WMSYS.wm_concat(t) from a1 group by i;
 i |   wm_concat
---+----------------
 1 | u1,u2,u3,u4
 2 | u5,u6,u7,u8,u9
(2 rows)

just adding another argument:

create or replace function WMSYS.sf_concat(text,text,text) returns text as $$
  select case when coalesce($1, '') <> '' then $1||$3||$2 else $2 end;
$$ language sql called on null input;

create aggregate WMSYS.wm_concat (text,text) (sfunc=WMSYS.sf_concat,stype=text);

t=# select i,WMSYS.wm_concat(t,':') from a1 group by i;
 i |   wm_concat
---+----------------
 1 | u1:u2:u3:u4
 2 | u5:u6:u7:u8:u9
(2 rows)

now, I don't know how well window functions will work in 8.3 and I have no env to try if such would work:

t=# select i, max(t) from (select i,WMSYS.wm_concat(t,':') over (partition by i order by t desc) t from a1) a group by i;
 i |      max
---+----------------
 1 | u4:u3:u2:u1
 2 | u9:u8:u7:u6:u5
(2 rows)

but as Roman has suggested, this should:

t=# select i,WMSYS.wm_concat(t,':') from (select * from a1 order by i asc,t desc) a group by i;
 i |   wm_concat
---+----------------
 1 | u4:u3:u2:u1
 2 | u9:u8:u7:u6:u5
(2 rows)

so you control ordering not as argument to aggregate function, but with the way you present data to it

Roman Tkachuk · Accepted Answer · 2017-09-16 21:20:11Z

0

Please try:

SELECT dept_id, replace(WMSYS.wm_concat(user_id, ',', ':')
  FROM (
        SELECT dept_id, user_id
          FROM dept_user
         ORDER BY 1, 2
       ) AS A
 GROUP BY dept_id;

answered Sep 16, 2017 at 21:20

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

2 Comments

lionyu Over a year ago

This can do what I want, but I hope WMSYS.wm_concat have parameter to control join string and sort, then the function will be easy to reuse.

Emilio Platzer Over a year ago

Warning. With this soluction a,1 joined with b,2 were a:1:b:2 instead of a,1:b,2.

Collectives™ on Stack Overflow

postgresql create aggregate with order by and default parameter

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related