Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9ea9918

Browse files
committed
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the samething, but inserts delimiters between elements.Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.
1 parentee3a81f commit9ea9918

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
intL,
7475
boollength_not_specified);
7576
staticbytea*bytea_overlay(bytea*t1,bytea*t2,intsp,intsl);
77+
staticStringInfomakeStringAggState(fmNodePtrcontext);
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+
staticStringInfo
3331+
makeStringAggState(fmNodePtrcontext)
3332+
{
3333+
StringInfostate;
3334+
MemoryContextaggcontext;
3335+
MemoryContextoldcontext;
3336+
3337+
if (context&&IsA(context,AggState))
3338+
aggcontext= ((AggState*)context)->aggcontext;
3339+
elseif (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+
returnstate;
3354+
}
3355+
3356+
Datum
3357+
string_agg_transfn(PG_FUNCTION_ARGS)
3358+
{
3359+
StringInfostate;
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+
StringInfostate;
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+
elseif (!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+
StringInfostate;
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-
#defineCATALOG_VERSION_NO201001282
56+
#defineCATALOG_VERSION_NO201002011
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 -0142_null_ ));
223223
/* array */
224224
DATA(insert (2335array_agg_transfnarray_agg_finalfn02281_null_ ));
225225

226+
/* text */
227+
DATA(insert (3537string_agg_transfnstring_agg_finalfn02281_null_ ));
228+
DATA(insert (3538string_agg_delim_transfnstring_agg_finalfn02281_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/2814: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_sampPGNSP PGUID 12 1 0 0 f f f t f i 1
28292829
DESCR("COVAR_SAMP(double, double) aggregate final function");
28302830
DATA(insertOID=2817 (float8_corrPGNSPPGUID12100ffftfi10701"1022"_null__null__null__null_float8_corr_null__null__null_ ));
28312831
DESCR("CORR(double, double) aggregate final function");
2832+
DATA(insertOID=3534 (string_agg_transfnPGNSPPGUID12100fffffi202281"2281 25"_null__null__null__null_string_agg_transfn_null__null__null_ ));
2833+
DESCR("string_agg(text) transition function");
2834+
DATA(insertOID=3535 (string_agg_delim_transfnPGNSPPGUID12100fffffi302281"2281 25 25"_null__null__null__null_string_agg_delim_transfn_null__null__null_ ));
2835+
DESCR("string_agg(text, text) transition function");
2836+
DATA(insertOID=3536 (string_agg_finalfnPGNSPPGUID12100fffffi1025"2281"_null__null__null__null_string_agg_finalfn_null__null__null_ ));
2837+
DESCR("string_agg final function");
2838+
DATA(insertOID=3537 (string_aggPGNSPPGUID12100tffffi1025"25"_null__null__null__null_aggregate_dummy_null__null__null_ ));
2839+
DESCR("concatenate aggregate input into an string");
2840+
DATA(insertOID=3538 (string_aggPGNSPPGUID12100tffffi2025"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(insertOID=1845 (to_asciiPGNSPPGUID12100ffftfi1025"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
externDatumpg_column_size(PG_FUNCTION_ARGS);
726726

727+
externDatumstring_agg_transfn(PG_FUNCTION_ARGS);
728+
externDatumstring_agg_delim_transfn(PG_FUNCTION_ARGS);
729+
externDatumstring_agg_finalfn(PG_FUNCTION_ARGS);
730+
727731
/* version.c */
728732
externDatumpgsql_version(PG_FUNCTION_ARGS);
729733

@@ -772,6 +776,9 @@ extern Datum translate(PG_FUNCTION_ARGS);
772776
externDatumchr (PG_FUNCTION_ARGS);
773777
externDatumrepeat(PG_FUNCTION_ARGS);
774778
externDatumascii(PG_FUNCTION_ARGS);
779+
externDatumstring_agg_transfn(PG_FUNCTION_ARGS);
780+
externDatumstring_agg_delim_transfn(PG_FUNCTION_ARGS);
781+
externDatumstring_agg_finalfn(PG_FUNCTION_ARGS);
775782

776783
/* inet_net_ntop.c */
777784
externchar*inet_net_ntop(intaf,constvoid*src,intbits,

‎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,corder 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)
[8]ページ先頭
©2009-2025 Movatter.jp