From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | proposal for 8.5, listagg aggregate function, WIP patch |
Date: | 2009-12-25 18:29:50 |
Message-ID: | 162867790912251029h2d0a7c5co13ae2a683ea521be@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello
I am returning to discussion
http://archives.postgresql.org/pgsql-hackers/2009-12/msg01378.php
I propose a new aggregate function - listagg. This function
concatenate values to string. If this function is used with two
parameters, then second parameter is used as delimiter. NULL input
values are ignored like other aggregates. If all values are NULL, then
result is NULL. When delimiter is omitted, then values are
concatenated without any delimiter.
This function could be replaced with
array_to_string(array_agg(),delimiter). It has same functionality, but
different implementation. listagg should be (and it is) faster,
because hasn't array overhead. In my tests - listagg is about 25%
faster. Mainly, listagg is shorter. Because we cannot well wrap
aggregates, I propose integrate this function. There are precedent -
function generate_sequences. It should be replaced by
generate_series(array_lower(), array_upper()), but it hasn't same
effectiveness.
Using:
postgres=# select * from country ;
town | state
------------+-------
Prague | cs
Brno | cs
Bratislava | sk
Kosice | sk
(4 rows)
postgres=# select listagg(town,',') from country group by state;
listagg
-------------------
Bratislava,Kosice
Prague,Brno
(2 rows)
Comments?
Regards
Pavel Stehule
Attachment | Content-Type | Size |
---|---|---|
listagg.diff | text/x-patch | 10.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2009-12-25 18:36:38 | Re: PQescapeByteaConn and the new hex encoding |
Previous Message | Matteo Beccati | 2009-12-25 18:17:43 | Re: PQescapeByteaConn and the new hex encoding |