proposal for 8.5, listagg aggregate function, WIP patch

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

Responses

Browse pgsql-hackers by date

  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