diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 25 | ||||
| -rw-r--r-- | src/backend/utils/adt/varlena.c | 106 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 4 | ||||
| -rw-r--r-- | src/include/catalog/pg_aggregate.h | 6 | ||||
| -rw-r--r-- | src/include/catalog/pg_proc.h | 12 | ||||
| -rw-r--r-- | src/include/utils/builtins.h | 9 | ||||
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 31 | ||||
| -rw-r--r-- | src/test/regress/sql/aggregates.sql | 7 |
8 files changed, 193 insertions, 7 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f777f9f1650..bd1bf352206 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.498 2010/01/25 20:55:32 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.499 2010/02/01 03:14:43 itagaki Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -1789,6 +1789,10 @@ </tgroup> </table> + <para> + See also <xref linkend="functions-aggregate"> about the aggregate + function <function>string_agg</function>. + </para> <table id="conversion-names"> <title>Built-in Conversions</title> @@ -9837,6 +9841,25 @@ SELECT NULLIF(value, '(none)') ... </row> <row> + <entry> + <indexterm> + <primary>string_agg</primary> + </indexterm> + <function> + string_agg(<replaceable class="parameter">expression</replaceable> + [, <replaceable class="parameter">delimiter</replaceable> ] ) + </function> + </entry> + <entry> + <type>text</type> + </entry> + <entry> + <type>text</type> + </entry> + <entry>input values concatenated into a string, optionally with delimiters</entry> + </row> + + <row> <entry><function>sum(<replaceable class="parameter">expression</replaceable>)</function></entry> <entry> <type>smallint</type>, <type>int</type>, diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index 7a8abf14a83..096ca75bf93 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.174 2010/01/25 20:55:32 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.175 2010/02/01 03:14:43 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -21,6 +21,7 @@ #include "libpq/md5.h" #include "libpq/pqformat.h" #include "miscadmin.h" +#include "nodes/execnodes.h" #include "parser/scansup.h" #include "regex/regex.h" #include "utils/builtins.h" @@ -73,6 +74,7 @@ static bytea *bytea_substring(Datum str, int L, bool length_not_specified); static bytea *bytea_overlay(bytea *t1, bytea *t2, int sp, int sl); +static StringInfo makeStringAggState(fmNodePtr context); /***************************************************************************** @@ -3315,3 +3317,105 @@ pg_column_size(PG_FUNCTION_ARGS) PG_RETURN_INT32(result); } + +/* + * string_agg - Concatenates values and returns string. + * + * Syntax: string_agg(value text, delimiter text = '') RETURNS text + * + * Note: Any NULL values are ignored. The first-call delimiter isn't + * actually used at all, and on subsequent calls the delimiter precedes + * the associated value. + */ +static StringInfo +makeStringAggState(fmNodePtr context) +{ + StringInfo state; + MemoryContext aggcontext; + MemoryContext oldcontext; + + if (context && IsA(context, AggState)) + aggcontext = ((AggState *) context)->aggcontext; + else if (context && IsA(context, WindowAggState)) + aggcontext = ((WindowAggState *) context)->wincontext; + else + { + /* cannot be called directly because of internal-type argument */ + elog(ERROR, "string_agg_transfn called in non-aggregate context"); + aggcontext = NULL; /* keep compiler quiet */ + } + + /* Create state in aggregate context */ + oldcontext = MemoryContextSwitchTo(aggcontext); + state = makeStringInfo(); + MemoryContextSwitchTo(oldcontext); + + return state; +} + +Datum +string_agg_transfn(PG_FUNCTION_ARGS) +{ + StringInfo state; + + state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0); + + /* Append the element unless not null. */ + if (!PG_ARGISNULL(1)) + { + if (state == NULL) + state = makeStringAggState(fcinfo->context); + appendStringInfoText(state, PG_GETARG_TEXT_PP(1)); /* value */ + } + + /* + * The transition type for string_agg() is declared to be "internal", which + * is a pass-by-value type the same size as a pointer. + */ + PG_RETURN_POINTER(state); +} + +Datum +string_agg_delim_transfn(PG_FUNCTION_ARGS) +{ + StringInfo state; + + state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0); + + /* Append the value unless not null. */ + if (!PG_ARGISNULL(1)) + { + if (state == NULL) + state = makeStringAggState(fcinfo->context); + else if (!PG_ARGISNULL(2)) + appendStringInfoText(state, PG_GETARG_TEXT_PP(2)); /* delimiter */ + + appendStringInfoText(state, PG_GETARG_TEXT_PP(1)); /* value */ + } + + /* + * The transition type for string_agg() is declared to be "internal", which + * is a pass-by-value type the same size as a pointer. + */ + PG_RETURN_POINTER(state); +} + +Datum +string_agg_finalfn(PG_FUNCTION_ARGS) +{ + StringInfo state; + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + /* cannot be called directly because of internal-type argument */ + Assert(fcinfo->context && + (IsA(fcinfo->context, AggState) || + IsA(fcinfo->context, WindowAggState))); + + state = (StringInfo) PG_GETARG_POINTER(0); + if (state != NULL) + PG_RETURN_TEXT_P(cstring_to_text(state->data)); + else + PG_RETURN_NULL(); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 192f870b81a..e12ec58ed69 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.581 2010/01/28 23:21:12 petere Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.582 2010/02/01 03:14:43 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201001282 +#define CATALOG_VERSION_NO 201002011 #endif diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 88b783fcc32..c47adbe4755 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.70 2010/01/05 01:06:56 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.71 2010/02/01 03:14:43 itagaki Exp $ * * NOTES * the genbki.pl script reads this file and generates .bki @@ -223,6 +223,10 @@ DATA(insert ( 2901 xmlconcat2 - 0 142 _null_ )); /* array */ DATA(insert ( 2335 array_agg_transfn array_agg_finalfn 0 2281 _null_ )); +/* text */ +DATA(insert (3537 string_agg_transfn string_agg_finalfn 0 2281 _null_ )); +DATA(insert (3538 string_agg_delim_transfn string_agg_finalfn 0 2281 _null_ )); + /* * prototypes for functions in pg_aggregate.c */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index a30e0697581..ec45367b4ae 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.566 2010/01/28 14:25:41 mha Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.567 2010/02/01 03:14:44 itagaki Exp $ * * NOTES * 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 DESCR("COVAR_SAMP(double, double) aggregate final function"); 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_ )); DESCR("CORR(double, double) aggregate final function"); +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_ )); +DESCR("string_agg(text) transition function"); +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_ )); +DESCR("string_agg(text, text) transition function"); +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_ )); +DESCR("string_agg final function"); +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_ )); +DESCR("concatenate aggregate input into an string"); +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_ )); +DESCR("concatenate aggregate input into an string with delimiter"); /* To ASCII conversion */ 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_ )); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 11eb8acacc7..6381c3d7356 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.345 2010/01/25 20:55:32 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.346 2010/02/01 03:14:45 itagaki Exp $ * *------------------------------------------------------------------------- */ @@ -724,6 +724,10 @@ extern Datum unknownsend(PG_FUNCTION_ARGS); extern Datum pg_column_size(PG_FUNCTION_ARGS); +extern Datum string_agg_transfn(PG_FUNCTION_ARGS); +extern Datum string_agg_delim_transfn(PG_FUNCTION_ARGS); +extern Datum string_agg_finalfn(PG_FUNCTION_ARGS); + /* version.c */ extern Datum pgsql_version(PG_FUNCTION_ARGS); @@ -772,6 +776,9 @@ extern Datum translate(PG_FUNCTION_ARGS); extern Datum chr (PG_FUNCTION_ARGS); extern Datum repeat(PG_FUNCTION_ARGS); extern Datum ascii(PG_FUNCTION_ARGS); +extern Datum string_agg_transfn(PG_FUNCTION_ARGS); +extern Datum string_agg_delim_transfn(PG_FUNCTION_ARGS); +extern Datum string_agg_finalfn(PG_FUNCTION_ARGS); /* inet_net_ntop.c */ extern char *inet_net_ntop(int af, const void *src, int bits, diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 9669a52fbea..2460d9dfd62 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -799,3 +799,34 @@ select aggfns(distinct a,a,c order by a,b) ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 1: select aggfns(distinct a,a,c order by a,b) ^ +-- string_agg tests +select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a); + string_agg +-------------- + aaaabbbbcccc +(1 row) + +select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); + string_agg +---------------- + aaaa,bbbb,cccc +(1 row) + +select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a); + string_agg +---------------- + aaaa,bbbb,cccc +(1 row) + +select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a); + string_agg +------------ + bbbb,cccc +(1 row) + +select string_agg(a,',') from (values(null),(null)) g(a); + string_agg +------------ + +(1 row) + diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 18f2e57b72b..daa89167a20 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -355,3 +355,10 @@ select aggfns(distinct a,b,c order by a,b,i,c) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; select aggfns(distinct a,a,c order by a,b) from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; + +-- string_agg tests +select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a); +select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); +select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a); +select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a); +select string_agg(a,',') from (values(null),(null)) g(a); |