Skip to content

Commit 9ea9918

Browse files
ItGackyItGacky
committed
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the same thing, but inserts delimiters between elements. Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.
1 parent ee3a81f commit 9ea9918

File tree

8 files changed

+193
-7
lines changed

8 files changed

+193
-7
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.498 2010/01/25 20:55:32 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.499 2010/02/01 03:14:43 itagaki Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -1789,6 +1789,10 @@
17891789
</tgroup>
17901790
</table>
17911791

1792+
<para>
1793+
See also <xref linkend="functions-aggregate"> about the aggregate
1794+
function <function>string_agg</function>.
1795+
</para>
17921796

17931797
<table id="conversion-names">
17941798
<title>Built-in Conversions</title>
@@ -9836,6 +9840,25 @@ SELECT NULLIF(value, '(none)') ...
98369840
</entry>
98379841
</row>
98389842

9843+
<row>
9844+
<entry>
9845+
<indexterm>
9846+
<primary>string_agg</primary>
9847+
</indexterm>
9848+
<function>
9849+
string_agg(<replaceable class="parameter">expression</replaceable>
9850+
[, <replaceable class="parameter">delimiter</replaceable> ] )
9851+
</function>
9852+
</entry>
9853+
<entry>
9854+
<type>text</type>
9855+
</entry>
9856+
<entry>
9857+
<type>text</type>
9858+
</entry>
9859+
<entry>input values concatenated into a string, optionally with delimiters</entry>
9860+
</row>
9861+
98399862
<row>
98409863
<entry><function>sum(<replaceable class="parameter">expression</replaceable>)</function></entry>
98419864
<entry>

‎src/backend/utils/adt/varlena.c

Lines changed: 105 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.174 2010/01/25 20:55:32 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.175 2010/02/01 03:14:43 itagaki Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -21,6 +21,7 @@
2121
#include "libpq/md5.h"
2222
#include "libpq/pqformat.h"
2323
#include "miscadmin.h"
24+
#include "nodes/execnodes.h"
2425
#include "parser/scansup.h"
2526
#include "regex/regex.h"
2627
#include "utils/builtins.h"
@@ -73,6 +74,7 @@ static bytea *bytea_substring(Datum str,
7374
int L,
7475
bool length_not_specified);
7576
static bytea *bytea_overlay(bytea *t1, bytea *t2, int sp, int sl);
77+
static StringInfo makeStringAggState(fmNodePtr context);
7678

7779

7880
/*****************************************************************************
@@ -3315,3 +3317,105 @@ pg_column_size(PG_FUNCTION_ARGS)
33153317

33163318
PG_RETURN_INT32(result);
33173319
}
3320+
3321+
/*
3322+
* string_agg - Concatenates values and returns string.
3323+
*
3324+
* Syntax: string_agg(value text, delimiter text = '') RETURNS text
3325+
*
3326+
* Note: Any NULL values are ignored. The first-call delimiter isn't
3327+
* actually used at all, and on subsequent calls the delimiter precedes
3328+
* the associated value.
3329+
*/
3330+
static StringInfo
3331+
makeStringAggState(fmNodePtr context)
3332+
{
3333+
StringInfo state;
3334+
MemoryContext aggcontext;
3335+
MemoryContext oldcontext;
3336+
3337+
if (context && IsA(context, AggState))
3338+
aggcontext = ((AggState *) context)->aggcontext;
3339+
else if (context && IsA(context, WindowAggState))
3340+
aggcontext = ((WindowAggState *) context)->wincontext;
3341+
else
3342+
{
3343+
/* cannot be called directly because of internal-type argument */
3344+
elog(ERROR, "string_agg_transfn called in non-aggregate context");
3345+
aggcontext = NULL; /* keep compiler quiet */
3346+
}
3347+
3348+
/* Create state in aggregate context */
3349+
oldcontext = MemoryContextSwitchTo(aggcontext);
3350+
state = makeStringInfo();
3351+
MemoryContextSwitchTo(oldcontext);
3352+
3353+
return state;
3354+
}
3355+
3356+
Datum
3357+
string_agg_transfn(PG_FUNCTION_ARGS)
3358+
{
3359+
StringInfo state;
3360+
3361+
state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0);
3362+
3363+
/* Append the element unless not null. */
3364+
if (!PG_ARGISNULL(1))
3365+
{
3366+
if (state == NULL)
3367+
state = makeStringAggState(fcinfo->context);
3368+
appendStringInfoText(state, PG_GETARG_TEXT_PP(1)); /* value */
3369+
}
3370+
3371+
/*
3372+
* The transition type for string_agg() is declared to be "internal", which
3373+
* is a pass-by-value type the same size as a pointer.
3374+
*/
3375+
PG_RETURN_POINTER(state);
3376+
}
3377+
3378+
Datum
3379+
string_agg_delim_transfn(PG_FUNCTION_ARGS)
3380+
{
3381+
StringInfo state;
3382+
3383+
state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0);
3384+
3385+
/* Append the value unless not null. */
3386+
if (!PG_ARGISNULL(1))
3387+
{
3388+
if (state == NULL)
3389+
state = makeStringAggState(fcinfo->context);
3390+
else if (!PG_ARGISNULL(2))
3391+
appendStringInfoText(state, PG_GETARG_TEXT_PP(2)); /* delimiter */
3392+
3393+
appendStringInfoText(state, PG_GETARG_TEXT_PP(1)); /* value */
3394+
}
3395+
3396+
/*
3397+
* The transition type for string_agg() is declared to be "internal", which
3398+
* is a pass-by-value type the same size as a pointer.
3399+
*/
3400+
PG_RETURN_POINTER(state);
3401+
}
3402+
3403+
Datum
3404+
string_agg_finalfn(PG_FUNCTION_ARGS)
3405+
{
3406+
StringInfo state;
3407+
3408+
if (PG_ARGISNULL(0))
3409+
PG_RETURN_NULL();
3410+
3411+
/* cannot be called directly because of internal-type argument */
3412+
Assert(fcinfo->context &&
3413+
(IsA(fcinfo->context, AggState) ||
3414+
IsA(fcinfo->context, WindowAggState)));
3415+
3416+
state = (StringInfo) PG_GETARG_POINTER(0);
3417+
if (state != NULL)
3418+
PG_RETURN_TEXT_P(cstring_to_text(state->data));
3419+
else
3420+
PG_RETURN_NULL();
3421+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.581 2010/01/28 23:21:12 petere Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.582 2010/02/01 03:14:43 itagaki Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201001282
56+
#define CATALOG_VERSION_NO 201002011
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.70 2010/01/05 01:06:56 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.71 2010/02/01 03:14:43 itagaki Exp $
1212
*
1313
* NOTES
1414
* the genbki.pl script reads this file and generates .bki
@@ -223,6 +223,10 @@ DATA(insert ( 2901 xmlconcat2 - 0 142 _null_ ));
223223
/* array */
224224
DATA(insert ( 2335 array_agg_transfn array_agg_finalfn 0 2281 _null_ ));
225225

226+
/* text */
227+
DATA(insert (3537 string_agg_transfn string_agg_finalfn 0 2281 _null_ ));
228+
DATA(insert (3538 string_agg_delim_transfn string_agg_finalfn 0 2281 _null_ ));
229+
226230
/*
227231
* prototypes for functions in pg_aggregate.c
228232
*/

‎src/include/catalog/pg_proc.h

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.566 2010/01/28 14:25:41 mha Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.567 2010/02/01 03:14:44 itagaki Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.pl reads this file and generates .bki
@@ -2829,6 +2829,16 @@ DATA(insert OID = 2816 ( float8_covar_samp PGNSP PGUID 12 1 0 0 f f f t f i 1
28292829
DESCR("COVAR_SAMP(double, double) aggregate final function");
28302830
DATA(insert OID = 2817 ( float8_corr PGNSP PGUID 12 1 0 0 f f f t f i 1 0 701 "1022" _null_ _null_ _null_ _null_ float8_corr _null_ _null_ _null_ ));
28312831
DESCR("CORR(double, double) aggregate final function");
2832+
DATA(insert OID = 3534 ( string_agg_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 25" _null_ _null_ _null_ _null_ string_agg_transfn _null_ _null_ _null_ ));
2833+
DESCR("string_agg(text) transition function");
2834+
DATA(insert OID = 3535 ( string_agg_delim_transfn PGNSP PGUID 12 1 0 0 f f f f f i 3 0 2281 "2281 25 25" _null_ _null_ _null_ _null_ string_agg_delim_transfn _null_ _null_ _null_ ));
2835+
DESCR("string_agg(text, text) transition function");
2836+
DATA(insert OID = 3536 ( string_agg_finalfn PGNSP PGUID 12 1 0 0 f f f f f i 1 0 25 "2281" _null_ _null_ _null_ _null_ string_agg_finalfn _null_ _null_ _null_ ));
2837+
DESCR("string_agg final function");
2838+
DATA(insert OID = 3537 ( string_agg PGNSP PGUID 12 1 0 0 t f f f f i 1 0 25 "25" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
2839+
DESCR("concatenate aggregate input into an string");
2840+
DATA(insert OID = 3538 ( string_agg PGNSP PGUID 12 1 0 0 t f f f f i 2 0 25 "25 25" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
2841+
DESCR("concatenate aggregate input into an string with delimiter");
28322842

28332843
/* To ASCII conversion */
28342844
DATA(insert OID = 1845 ( to_ascii PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "25" _null_ _null_ _null_ _null_ to_ascii_default _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.345 2010/01/25 20:55:32 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.346 2010/02/01 03:14:45 itagaki Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -724,6 +724,10 @@ extern Datum unknownsend(PG_FUNCTION_ARGS);
724724

725725
extern Datum pg_column_size(PG_FUNCTION_ARGS);
726726

727+
extern Datum string_agg_transfn(PG_FUNCTION_ARGS);
728+
extern Datum string_agg_delim_transfn(PG_FUNCTION_ARGS);
729+
extern Datum string_agg_finalfn(PG_FUNCTION_ARGS);
730+
727731
/* version.c */
728732
extern Datum pgsql_version(PG_FUNCTION_ARGS);
729733

@@ -772,6 +776,9 @@ extern Datum translate(PG_FUNCTION_ARGS);
772776
extern Datum chr (PG_FUNCTION_ARGS);
773777
extern Datum repeat(PG_FUNCTION_ARGS);
774778
extern Datum ascii(PG_FUNCTION_ARGS);
779+
extern Datum string_agg_transfn(PG_FUNCTION_ARGS);
780+
extern Datum string_agg_delim_transfn(PG_FUNCTION_ARGS);
781+
extern Datum string_agg_finalfn(PG_FUNCTION_ARGS);
775782

776783
/* inet_net_ntop.c */
777784
extern char *inet_net_ntop(int af, const void *src, int bits,

‎src/test/regress/expected/aggregates.out

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -799,3 +799,34 @@ select aggfns(distinct a,a,c order by a,b)
799799
ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
800800
LINE 1: select aggfns(distinct a,a,c order by a,b)
801801
^
802+
-- string_agg tests
803+
select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
804+
string_agg
805+
--------------
806+
aaaabbbbcccc
807+
(1 row)
808+
809+
select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
810+
string_agg
811+
----------------
812+
aaaa,bbbb,cccc
813+
(1 row)
814+
815+
select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
816+
string_agg
817+
----------------
818+
aaaa,bbbb,cccc
819+
(1 row)
820+
821+
select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
822+
string_agg
823+
------------
824+
bbbb,cccc
825+
(1 row)
826+
827+
select string_agg(a,',') from (values(null),(null)) g(a);
828+
string_agg
829+
------------
830+
831+
(1 row)
832+

‎src/test/regress/sql/aggregates.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -355,3 +355,10 @@ select aggfns(distinct a,b,c order by a,b,i,c)
355355
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
356356
select aggfns(distinct a,a,c order by a,b)
357357
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
358+
359+
-- string_agg tests
360+
select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
361+
select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
362+
select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
363+
select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
364+
select string_agg(a,',') from (values(null),(null)) g(a);

0 commit comments

Comments
 (0)