Hi,
Here's a patch for the aggregate function outlined by Corey Huinker in
CADkLM=foA_oC_Ri23F9PbfLnfwXFbC3Lt8bBzRu3=cb77g9...@mail.gmail.com . I
called it "onlyvalue", which is a horrible name, but I have nothing
better to offer. (Corey called it "only", but that doesn't really work
since ONLY is a fully reserved keyword.)
I'll add this to September's commit fest, but if you want to bash me or
the patch in the meanwhile, go ahead.
.m
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2946122..6edc220 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12631,6 +12631,26 @@ NULL baz</literallayout>(3 rows)</entry>
<row>
<entry>
<indexterm>
+ <primary>onlyvalue</primary>
+ </indexterm>
+ <function>
+ onlyvalue(<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 non-NULL value from the input
+ values; if any of the input values is NULL or more than one distinct
+ value exists, an exception is raised</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>string_agg</primary>
</indexterm>
<function>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..72bb55c 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -40,6 +40,7 @@
#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,3 +599,93 @@ pg_column_is_updatable(PG_FUNCTION_ARGS)
PG_RETURN_BOOL((events & REQ_EVENTS) == REQ_EVENTS);
}
+
+struct onlyvalue_agg_stype
+{
+ FunctionCallInfoData fcinfo;
+ Datum datum;
+};
+
+Datum
+onlyvalue_agg_transfn(PG_FUNCTION_ARGS)
+{
+ Oid arg1_typeid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ MemoryContext aggcontext;
+ struct onlyvalue_agg_stype *state;
+
+ if (arg1_typeid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "onlyvalue_agg_transfn called in non-aggregate context");
+ }
+
+ if (PG_ARGISNULL(1))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("NULL value passed to onlyvalue")));
+ }
+
+ if (PG_ARGISNULL(0))
+ {
+ TypeCacheEntry *typentry;
+
+ state = (struct onlyvalue_agg_stype *) MemoryContextAlloc(aggcontext, sizeof(struct onlyvalue_agg_stype));
+ state->datum = PG_GETARG_DATUM(1);
+
+ 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))));
+
+ InitFunctionCallInfoData(state->fcinfo, &typentry->eq_opr_finfo, 2,
+ InvalidOid, NULL, NULL);
+ }
+ else
+ {
+ bool oprresult;
+
+ state = (struct onlyvalue_agg_stype *) PG_GETARG_POINTER(0);
+
+ 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 onlyvalue")));
+ }
+
+ /*
+ * The transition type for onlyvalue() 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
+onlyvalue_agg_finalfn(PG_FUNCTION_ARGS)
+{
+ struct onlyvalue_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 onlyvalue_agg_stype *) PG_GETARG_POINTER(0);
+ PG_RETURN_DATUM(state->datum);
+}
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index dd6079f..9d6c44a 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -292,6 +292,9 @@ DATA(insert ( 3197 n 0 json_object_agg_transfn json_object_agg_finalfn - -
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_ ));
+/* onlyvalue */
+DATA(insert ( 4202 n 0 onlyvalue_agg_transfn onlyvalue_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_ ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f688454..f1361ee 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -944,6 +944,12 @@ DATA(insert OID = 4053 ( array_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i s 1
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 ( onlyvalue_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_ onlyvalue_agg_transfn _null_ _null_ _null_ ));
+DESCR("aggregate transition function");
+DATA(insert OID = 4201 ( onlyvalue_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_ onlyvalue_agg_finalfn _null_ _null_ _null_ ));
+DESCR("aggregate final function");
+DATA(insert OID = 4202 ( onlyvalue 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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index fc1679e..0b8e9e8 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -495,6 +495,8 @@ extern Datum pg_typeof(PG_FUNCTION_ARGS);
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 onlyvalue_agg_transfn(PG_FUNCTION_ARGS);
+extern Datum onlyvalue_agg_finalfn(PG_FUNCTION_ARGS);
/* oid.c */
extern Datum oidin(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index de826b5..12dd519 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1784,3 +1784,38 @@ NOTICE: sum_transfn called with 4
(1 row)
rollback;
+/* onlyvalue */
+select onlyvalue(vv.v) from (values (1), (1)) vv(v);
+ onlyvalue
+-----------
+ 1
+(1 row)
+
+select onlyvalue(vv.v) from (values (1), (2)) vv(v);
+ERROR: more than one distinct value passed to onlyvalue
+select onlyvalue(vv.v) from (values (1), (NULL)) vv(v);
+ERROR: NULL value passed to onlyvalue
+select onlyvalue(vv.v) from (values (json '{}')) vv(v);
+ERROR: could not identify an equality operator for type json
+select onlyvalue(vv.v1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ERROR: more than one distinct value passed to onlyvalue
+select vv.v2, onlyvalue(vv.v1) from (values (1,1), (2,2), (2,2), (3,3)) vv(v1, v2) group by vv.v2;
+ v2 | onlyvalue
+----+-----------
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
+select onlyvalue(vv.v1) FILTER (WHERE vv.v2 = 1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+ onlyvalue
+-----------
+ 1
+(1 row)
+
+select onlyvalue(vv.v) from (select 1) vv(V) where false;
+ onlyvalue
+-----------
+
+(1 row)
+
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 8d501dc..9aba5df 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -755,3 +755,13 @@ create aggregate my_half_sum(int4)
select my_sum(one),my_half_sum(one) from (values(1),(2),(3),(4)) t(one);
rollback;
+
+/* onlyvalue */
+select onlyvalue(vv.v) from (values (1), (1)) vv(v);
+select onlyvalue(vv.v) from (values (1), (2)) vv(v);
+select onlyvalue(vv.v) from (values (1), (NULL)) vv(v);
+select onlyvalue(vv.v) from (values (json '{}')) vv(v);
+select onlyvalue(vv.v1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+select vv.v2, onlyvalue(vv.v1) from (values (1,1), (2,2), (2,2), (3,3)) vv(v1, v2) group by vv.v2;
+select onlyvalue(vv.v1) FILTER (WHERE vv.v2 = 1) from (values (1,1), (2,2), (3,3)) vv(v1, v2);
+select onlyvalue(vv.v) from (select 1) vv(V) where false;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
