listagg.patch
application/octet-stream
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 0 | 0 |
| src/backend/utils/adt/varchar.c | 0 | 0 |
| src/include/catalog/pg_aggregate.h | 0 | 0 |
| src/include/catalog/pg_proc.h | 0 | 0 |
| src/include/utils/builtins.h | 0 | 0 |
| src/test/regress/expected/aggregates.out | 0 | 0 |
| src/test/regress/sql/aggregates.sql | 0 | 0 |
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5de44d6..a437d2c 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 1789,1794 ****
--- 1789,1798 ----
</tgroup>
</table>
+ <para>
+ See also <xref linkend="functions-aggregate"> about the aggregate
+ function <function>listagg</function>.
+ </para>
<table id="conversion-names">
<title>Built-in Conversions</title>
*************** SELECT NULLIF(value, '(none)') ...
*** 9789,9794 ****
--- 9793,9816 ----
</row>
<row>
+ <entry>
+ <indexterm>
+ <primary>listagg</primary>
+ </indexterm>
+ <function>
+ listagg(<replaceable class="parameter">expression</replaceable>
+ [, <replaceable class="parameter">expression</replaceable> ] )</function>
+ </entry>
+ <entry>
+ <type>text</type>
+ </entry>
+ <entry>
+ <type>text</type>
+ </entry>
+ <entry>input values concatenated into an string, optionally separated by the second argument</entry>
+ </row>
+
+ <row>
<entry><function>max(<replaceable class="parameter">expression</replaceable>)</function></entry>
<entry>any array, numeric, string, or date/time type</entry>
<entry>same as argument type</entry>
diff --git a/src/backend/utils/adt/varchar.c b/src/backend/utils/adt/varchar.c
index cbc08f7..cd13ffd 100644
*** a/src/backend/utils/adt/varchar.c
--- b/src/backend/utils/adt/varchar.c
***************
*** 18,27 ****
--- 18,40 ----
#include "access/hash.h"
#include "access/tuptoaster.h"
#include "libpq/pqformat.h"
+ #include "lib/stringinfo.h"
+ #include "nodes/execnodes.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "mb/pg_wchar.h"
+ /* type for state data of listagg function */
+ typedef struct
+ {
+ StringInfo strInfo;
+ char delimiter[1]; /* separator string - one or more chars */
+ } ListAggState;
+
+ static ListAggState *accumStringResult(ListAggState *state,
+ text *elem,
+ text *delimiter,
+ MemoryContext aggcontext);
/* common code for bpchartypmodin and varchartypmodin */
static int32
*************** btbpchar_pattern_cmp(PG_FUNCTION_ARGS)
*** 995,997 ****
--- 1008,1163 ----
PG_RETURN_INT32(result);
}
+
+ /****************************************************************
+ * listagg
+ *
+ * Concates values and returns string.
+ *
+ * Syntax:
+ * FUNCTION listagg(string varchar, delimiter varchar = '')
+ * RETURNS varchar;
+ *
+ * Note: any NULL value is ignored.
+ *
+ ****************************************************************/
+ static ListAggState *
+ accumStringResult(ListAggState *state, text *elem, text *delimiter,
+ MemoryContext aggcontext)
+ {
+ MemoryContext oldcontext;
+
+ /*
+ * when state is NULL, create new state value.
+ */
+ if (state == NULL)
+ {
+ if (delimiter != NULL)
+ {
+ char *dstr = text_to_cstring(delimiter);
+ int len = strlen(dstr);
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = palloc(sizeof(ListAggState) + len);
+
+ /* copy delimiter to state var */
+ memcpy(&state->delimiter, dstr, len + 1);
+ }
+ else
+ {
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = palloc(sizeof(ListAggState));
+ state->delimiter[0] = '\0';
+ }
+
+ /* Initialise StringInfo */
+ state->strInfo = NULL;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* only when element isn't null */
+ if (elem != NULL)
+ {
+ char *value = text_to_cstring(elem);
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ if (state->strInfo != NULL)
+ appendStringInfoString(state->strInfo, state->delimiter);
+ else
+ state->strInfo = makeStringInfo();
+
+ appendStringInfoString(state->strInfo, value);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ return state;
+ }
+
+ Datum
+ listagg1_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ListAggState *state = NULL;
+ text *elem;
+
+ if (fcinfo->context && IsA(fcinfo->context, AggState))
+ aggcontext = ((AggState *) fcinfo->context)->aggcontext;
+ else if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ aggcontext = ((WindowAggState *) fcinfo->context)->wincontext;
+ else
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "listagg2_transfn called in non-aggregate context");
+ aggcontext = NULL; /* keep compiler quiet */
+ }
+
+ state = PG_ARGISNULL(0) ? NULL : (ListAggState *) PG_GETARG_POINTER(0);
+ elem = PG_ARGISNULL(1) ? NULL : PG_GETARG_TEXT_P(1);
+
+ state = accumStringResult(state,
+ elem,
+ NULL,
+ aggcontext);
+
+ /*
+ * The transition type for listagg() is declared to be "internal", which
+ * is a pass-by-value type the same size as a pointer.
+ */
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ listagg2_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ListAggState *state = NULL;
+ text *elem;
+ text *delimiter = NULL;
+
+ if (fcinfo->context && IsA(fcinfo->context, AggState))
+ aggcontext = ((AggState *) fcinfo->context)->aggcontext;
+ else if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ aggcontext = ((WindowAggState *) fcinfo->context)->wincontext;
+ else
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "listagg2_transfn called in non-aggregate context");
+ aggcontext = NULL; /* keep compiler quiet */
+ }
+
+ state = PG_ARGISNULL(0) ? NULL : (ListAggState *) PG_GETARG_POINTER(0);
+ elem = PG_ARGISNULL(1) ? NULL : PG_GETARG_TEXT_P(1);
+ delimiter = PG_ARGISNULL(2) ? NULL : PG_GETARG_TEXT_P(2);
+
+ state = accumStringResult(state,
+ elem,
+ delimiter,
+ aggcontext);
+
+ /*
+ * The transition type for listagg() is declared to be "internal", which
+ * is a pass-by-value type the same size as a pointer.
+ */
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ listagg_finalfn(PG_FUNCTION_ARGS)
+ {
+ ListAggState *state = NULL;
+
+ 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 = (ListAggState *) PG_GETARG_POINTER(0);
+ if (state->strInfo != NULL)
+ PG_RETURN_TEXT_P(cstring_to_text(state->strInfo->data));
+ else
+ PG_RETURN_NULL();
+ }
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 3d232d2..b352243 100644
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
*************** DATA(insert ( 2901 xmlconcat2 - 0
*** 223,228 ****
--- 223,232 ----
/* array */
DATA(insert ( 2335 array_agg_transfn array_agg_finalfn 0 2281 _null_ ));
+ /* varchar */
+ DATA(insert (3030 listagg1_transfn listagg_finalfn 0 2281 _null_ ));
+ DATA(insert (3031 listagg2_transfn listagg_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 c4320e5..db7a818 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("convert int4 number to hex");
*** 2252,2257 ****
--- 2252,2268 ----
DATA(insert OID = 2090 ( to_hex PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "20" _null_ _null_ _null_ _null_ to_hex64 _null_ _null_ _null_ ));
DESCR("convert int8 number to hex");
+ DATA(insert OID = 2995 ( listagg1_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 25" _null_ _null_ _null_ _null_ listagg1_transfn _null_ _null_ _null_ ));
+ DESCR("listagg one param transition function");
+ DATA(insert OID = 2996 ( listagg2_transfn PGNSP PGUID 12 1 0 0 f f f f f i 3 0 2281 "2281 25 25" _null_ _null_ _null_ _null_ listagg2_transfn _null_ _null_ _null_ ));
+ DESCR("listagg two params transition function");
+ DATA(insert OID = 2999 ( listagg_finalfn PGNSP PGUID 12 1 0 0 f f f f f i 1 0 25 "2281" _null_ _null_ _null_ _null_ listagg_finalfn _null_ _null_ _null_ ));
+ DESCR("listagg final function");
+ DATA(insert OID = 3030 ( listagg 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 = 3031 ( listagg 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");
+
/* for character set encoding support */
/* return database encoding name */
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 72e9ed0..076e713 100644
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum varchartypmodin(PG_FUNCTION
*** 666,671 ****
--- 666,675 ----
extern Datum varchartypmodout(PG_FUNCTION_ARGS);
extern Datum varchar(PG_FUNCTION_ARGS);
+ extern Datum listagg1_transfn(PG_FUNCTION_ARGS);
+ extern Datum listagg2_transfn(PG_FUNCTION_ARGS);
+ extern Datum listagg_finalfn(PG_FUNCTION_ARGS);
+
/* varlena.c */
extern text *cstring_to_text(const char *s);
extern text *cstring_to_text_with_len(const char *s, int len);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 9669a52..84a0c94 100644
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
*************** select aggfns(distinct a,a,c order by a,
*** 799,801 ****
--- 799,832 ----
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)
^
+ -- list agg test
+ select listagg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
+ listagg
+ --------------
+ aaaabbbbcccc
+ (1 row)
+
+ select listagg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
+ listagg
+ ----------------
+ aaaa,bbbb,cccc
+ (1 row)
+
+ select listagg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
+ listagg
+ ----------------
+ aaaa,bbbb,cccc
+ (1 row)
+
+ select listagg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
+ listagg
+ -----------
+ bbbb,cccc
+ (1 row)
+
+ select listagg(a,',') from (values(null),(null)) g(a);
+ listagg
+ ---------
+
+ (1 row)
+
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 18f2e57..fd165d6 100644
*** a/src/test/regress/sql/aggregates.sql
--- b/src/test/regress/sql/aggregates.sql
*************** select aggfns(distinct a,b,c order by a,
*** 355,357 ****
--- 355,364 ----
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;
+
+ -- list agg test
+ select listagg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
+ select listagg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
+ select listagg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
+ select listagg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
+ select listagg(a,',') from (values(null),(null)) g(a);