Hi Dean,
Here's v2 of the patch. How's this look?
.m
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 12635,12640 **** NULL baz</literallayout>(3 rows)</entry>
--- 12635,12660 ----
<row>
<entry>
<indexterm>
+ <primary>single_value</primary>
+ </indexterm>
+ <function>
+ single_value(<replaceable class="parameter">expression</replaceable>)
+ </function>
+ </entry>
+ <entry>
+ any type for which the equality operator has been defined
+ </entry>
+ <entry>
+ same as argument type
+ </entry>
+ <entry>returns the single distinct value from the input values;
+ if more than one distinct value exists (while considering NULLs equal),
+ an exception is raised</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>string_agg</primary>
</indexterm>
<function>
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 40,45 ****
--- 40,46 ----
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/timestamp.h"
+ #include "utils/typcache.h"
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
***************
*** 598,600 **** pg_column_is_updatable(PG_FUNCTION_ARGS)
--- 599,702 ----
PG_RETURN_BOOL((events & REQ_EVENTS) == REQ_EVENTS);
}
+
+ struct single_value_agg_stype
+ {
+ FunctionCallInfoData fcinfo;
+ Datum datum;
+ bool isnull;
+ };
+
+ Datum
+ single_value_agg_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ struct single_value_agg_stype *state;
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "single_value_agg_transfn called in non-aggregate context");
+ }
+
+ if (PG_ARGISNULL(0))
+ {
+ TypeCacheEntry *typentry;
+ Oid arg1_typeid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+
+ if (arg1_typeid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ typentry = lookup_type_cache(arg1_typeid,
+ TYPECACHE_EQ_OPR_FINFO);
+ if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be(arg1_typeid))));
+
+ state = (struct single_value_agg_stype *) MemoryContextAlloc(aggcontext, sizeof(struct single_value_agg_stype));
+
+ if (PG_ARGISNULL(1))
+ {
+ state->datum = (Datum) 0;
+ state->isnull = true;
+ memset(&state->fcinfo, 0, sizeof(state->fcinfo));
+ }
+ else
+ {
+ state->datum = PG_GETARG_DATUM(1);
+ state->isnull = false;
+ InitFunctionCallInfoData(state->fcinfo, &typentry->eq_opr_finfo, 2,
+ InvalidOid, NULL, NULL);
+ }
+ }
+ else
+ {
+ bool oprresult;
+
+ state = (struct single_value_agg_stype *) PG_GETARG_POINTER(0);
+
+ if (state->isnull)
+ oprresult = PG_ARGISNULL(1);
+ else
+ {
+ state->fcinfo.argnull[0] = false;
+ state->fcinfo.argnull[1] = false;
+ state->fcinfo.arg[0] = state->datum;
+ state->fcinfo.arg[1] = PG_GETARG_DATUM(1);
+ state->fcinfo.isnull = false;
+ oprresult = DatumGetBool(FunctionCallInvoke(&state->fcinfo));
+ }
+ if (!oprresult)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("more than one distinct value passed to single_value")));
+ }
+
+ /*
+ * The transition type for single_value() is declared to be "internal",
+ * which is a pass-by-value type the same size as a pointer. So we can
+ * safely pass the pointer through nodeAgg.c's machinations.
+ */
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ single_value_agg_finalfn(PG_FUNCTION_ARGS)
+ {
+ struct single_value_agg_stype *state;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ /* cannot be called directly because of internal-type argument */
+ Assert(AggCheckCallContext(fcinfo, NULL));
+
+ state = (struct single_value_agg_stype *) PG_GETARG_POINTER(0);
+ if (state->isnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(state->datum);
+ }
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
***************
*** 292,297 **** DATA(insert ( 3197 n 0 json_object_agg_transfn json_object_agg_finalfn - -
--- 292,300 ----
DATA(insert ( 3267 n 0 jsonb_agg_transfn jsonb_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ ));
DATA(insert ( 3270 n 0 jsonb_object_agg_transfn jsonb_object_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ ));
+ /* single_value */
+ DATA(insert ( 4202 n 0 single_value_agg_transfn single_value_agg_finalfn - - - t f 0 2281 0 0 0 _null_ _null_ ));
+
/* ordered-set and hypothetical-set aggregates */
DATA(insert ( 3972 o 1 ordered_set_transition percentile_disc_final - - - t f 0 2281 0 0 0 _null_ _null_ ));
DATA(insert ( 3974 o 1 ordered_set_transition percentile_cont_float8_final - - - f f 0 2281 0 0 0 _null_ _null_ ));
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 945,950 **** DATA(insert OID = 4053 ( array_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i s 1
--- 945,956 ----
DESCR("concatenate aggregate input into an array");
DATA(insert OID = 3218 ( width_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "2283 2277" _null_ _null_ _null_ _null_ _null_ width_bucket_array _null_ _null_ _null_ ));
DESCR("bucket number of operand given a sorted array of bucket lower bounds");
+ DATA(insert OID = 4200 ( single_value_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2283" _null_ _null_ _null_ _null_ _null_ single_value_agg_transfn _null_ _null_ _null_ ));
+ DESCR("aggregate transition function");
+ DATA(insert OID = 4201 ( single_value_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2283 "2281 2283" _null_ _null_ _null_ _null_ _null_ single_value_agg_finalfn _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 4202 ( single_value PGNSP PGUID 12 1 0 0 0 t f f f f f i s 1 0 2283 "2283" _null_ _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("return the single distinct value from aggregate input");
DATA(insert OID = 3816 ( array_typanalyze PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 16 "2281" _null_ _null_ _null_ _null_ _null_ array_typanalyze _null_ _null_ _null_ ));
DESCR("array typanalyze");
DATA(insert OID = 3817 ( arraycontsel PGNSP PGUID 12 1 0 0 0 f f f f t f s s 4 0 701 "2281 26 2281 23" _null_ _null_ _null_ _null_ _null_ arraycontsel _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 495,500 **** extern Datum pg_typeof(PG_FUNCTION_ARGS);
--- 495,502 ----
extern Datum pg_collation_for(PG_FUNCTION_ARGS);
extern Datum pg_relation_is_updatable(PG_FUNCTION_ARGS);
extern Datum pg_column_is_updatable(PG_FUNCTION_ARGS);
+ extern Datum single_value_agg_transfn(PG_FUNCTION_ARGS);
+ extern Datum single_value_agg_finalfn(PG_FUNCTION_ARGS);
/* oid.c */
extern Datum oidin(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
***************
*** 1784,1786 **** NOTICE: sum_transfn called with 4
--- 1784,1827 ----
(1 row)
rollback;
+ /* single_value */
+ select single_value(vv.v) from (values (1), (1)) vv(v);
+ single_value
+ --------------
+ 1
+ (1 row)
+
+ select single_value(vv.v) from (values (1), (2)) vv(v);
+ ERROR: more than one distinct value passed to single_value
+ select single_value(vv.v) from (values (1), (NULL)) vv(v);
+ ERROR: more than one distinct value passed to single_value
+ select single_value(vv.v) from (values (NULL), (NULL)) vv(v);
+ single_value
+ --------------
+
+ (1 row)
+
+ select single_value(vv.v) from (values (json '{}')) vv(v);
+ ERROR: could not identify an equality operator for type json
+ select single_value(vv.v1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ ERROR: more than one distinct value passed to single_value
+ select vv.v2, single_value(vv.v1) from (values (1,1), (2,2), (2,2), (3,3)) vv(v1, v2) group by vv.v2;
+ v2 | single_value
+ ----+--------------
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (3 rows)
+
+ select single_value(vv.v1) FILTER (WHERE vv.v2 = 1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ single_value
+ --------------
+ 1
+ (1 row)
+
+ select single_value(vv.v) from (select 1) vv(V) where false;
+ single_value
+ --------------
+
+ (1 row)
+
*** a/src/test/regress/sql/aggregates.sql
--- b/src/test/regress/sql/aggregates.sql
***************
*** 755,757 **** create aggregate my_half_sum(int4)
--- 755,768 ----
select my_sum(one),my_half_sum(one) from (values(1),(2),(3),(4)) t(one);
rollback;
+
+ /* single_value */
+ select single_value(vv.v) from (values (1), (1)) vv(v);
+ select single_value(vv.v) from (values (1), (2)) vv(v);
+ select single_value(vv.v) from (values (1), (NULL)) vv(v);
+ select single_value(vv.v) from (values (NULL), (NULL)) vv(v);
+ select single_value(vv.v) from (values (json '{}')) vv(v);
+ select single_value(vv.v1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ select vv.v2, single_value(vv.v1) from (values (1,1), (2,2), (2,2), (3,3)) vv(v1, v2) group by vv.v2;
+ select single_value(vv.v1) FILTER (WHERE vv.v2 = 1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ select single_value(vv.v) from (select 1) vv(V) where false;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
