Hi,
I'd like to suggest $SUBJECT for inclusion in Postgres 9.6. I'm sure
everyone would've found it useful at some point in their lives, and the
fact that it can't be properly implemented in any language other than C
I think speaks for the fact that we as a project should provide it.
A quick and dirty proof of concept (patch attached):
=# select count_nulls(null::int, null::text, 17, 'bar');
count_nulls
-------------
2
(1 row)
Its natural habitat would be CHECK constraints, e.g:
CHECK (count_nulls(a,b,c) IN (0, 3))
Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.
.m
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 45,50 ****
--- 45,119 ----
/*
+ * count_nulls()
+ * Count the number of NULL input arguments
+ */
+ Datum
+ pg_count_nulls(PG_FUNCTION_ARGS)
+ {
+ int32 count = 0;
+ int i;
+
+ if (get_fn_expr_variadic(fcinfo->flinfo))
+ {
+ ArrayType *arr;
+ int ndims, nitems, *dims;
+ bits8 *bitmap;
+ int bitmask;
+
+ /* Should have just the one argument */
+ Assert(PG_NARGS() == 1);
+
+ /* count_nulls(VARIADIC NULL) is defined as NULL */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ /*
+ * Non-null argument had better be an array. We assume that
any call
+ * context that could let get_fn_expr_variadic return true will
have
+ * checked that a VARIADIC-labeled parameter actually is an
array. So
+ * it should be okay to just Assert that it's an array rather
than
+ * doing a full-fledged error check.
+ */
+
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo,
0))));
+
+ /* OK, safe to fetch the array value */
+ arr = PG_GETARG_ARRAYTYPE_P(0);
+
+ ndims = ARR_NDIM(arr);
+ dims = ARR_DIMS(arr);
+ nitems = ArrayGetNItems(ndims, dims);
+ //if (nitems == 0)
+ // return PG_RETURN_INT32(0);
+
+ bitmap = ARR_NULLBITMAP(arr);
+ if (!bitmap)
+ PG_RETURN_INT32(0);
+
+ for (i = 0; i < nitems; i++)
+ {
+ if ((*bitmap & bitmask) == 0)
+ count++;
+
+ bitmask <<= 1;
+ if (bitmask == 0x100)
+ {
+ bitmap++;
+ bitmask = 1;
+ }
+ }
+ PG_RETURN_INT32(count);
+ }
+
+ for (i = 0; i < PG_NARGS(); i++)
+ {
+ if (PG_ARGISNULL(i))
+ count++;
+ }
+ PG_RETURN_INT32(count);
+ }
+
+ /*
* current_database()
* Expose the current database to the user
*/
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2959,2964 **** DESCR("adjust time with time zone precision");
--- 2959,2965 ----
DATA(insert OID = 2003 ( textanycat PGNSP PGUID 14 1 0 0 0 f f f f t f s
2 0 25 "25 2776" _null_ _null_ _null_ _null_ _null_ "select $1 ||
$2::pg_catalog.text" _null_ _null_ _null_ ));
DATA(insert OID = 2004 ( anytextcat PGNSP PGUID 14 1 0 0 0 f f f f t f s
2 0 25 "2776 25" _null_ _null_ _null_ _null_ _null_ "select $1::pg_catalog.text
|| $2" _null_ _null_ _null_ ));
+ DATA(insert OID = 3308 ( count_nulls PGNSP PGUID 12 1 0 2276 0 f f f f f
f i 1 0 23 "2276" "{2276}" "{v}" _null_ _null_ _null_ pg_count_nulls _null_
_null_ _null_ ));
DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 1 0 0 0 f f f
f t f i 2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ bytealike _null_
_null_ _null_ ));
DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 1 0 0 0 f f f f t f i
2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ byteanlike _null_ _null_
_null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 481,486 **** extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
--- 481,487 ----
extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
/* misc.c */
+ extern Datum pg_count_nulls(PG_FUNCTION_ARGS);
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum current_query(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
