On 2015-11-21 06:02, I wrote:
Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.
I forgot to update the some references in the documentation. Fixed in
v3, attached.
.m
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 182,188 ****
</sect1>
<sect1 id="functions-comparison">
! <title>Comparison Operators</title>
<indexterm zone="functions-comparison">
<primary>comparison</primary>
--- 182,188 ----
</sect1>
<sect1 id="functions-comparison">
! <title>Comparison Functions and Operators</title>
<indexterm zone="functions-comparison">
<primary>comparison</primary>
***************
*** 191,200 ****
<para>
The usual comparison operators are available, shown in <xref
! linkend="functions-comparison-table">.
</para>
! <table id="functions-comparison-table">
<title>Comparison Operators</title>
<tgroup cols="2">
<thead>
--- 191,200 ----
<para>
The usual comparison operators are available, shown in <xref
! linkend="functions-comparison-op-table">.
</para>
! <table id="functions-comparison-op-table">
<title>Comparison Operators</title>
<tgroup cols="2">
<thead>
***************
*** 437,442 ****
--- 437,470 ----
</para>
-->
+ <table id="functions-comparison-table">
+ <title>Comparison Functions</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Example Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>num_nulls</primary>
+ </indexterm>
+ <literal>num_nulls(VARIADIC "any")</literal>
+ </entry>
+ <entry>Returns the number of NULL input arguments</entry>
+ <entry><literal>num_nulls(1, NULL, 2)</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+
</sect1>
<sect1 id="functions-math">
***************
*** 10307,10313 **** table2-mapping
</note>
<para>
The standard comparison operators shown in <xref
! linkend="functions-comparison-table"> are available for
<type>jsonb</type>, but not for <type>json</type>. They follow the
ordering rules for B-tree operations outlined at <xref
linkend="json-indexing">.
--- 10335,10341 ----
</note>
<para>
The standard comparison operators shown in <xref
! linkend="functions-comparison-op-table"> are available for
<type>jsonb</type>, but not for <type>json</type>. They follow the
ordering rules for B-tree operations outlined at <xref
linkend="json-indexing">.
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 45,50 ****
--- 45,118 ----
/*
+ * num_nulls()
+ * Count the number of NULL input arguments
+ */
+ Datum
+ pg_num_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);
+
+ /* num_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);
+
+ bitmap = ARR_NULLBITMAP(arr);
+ if (!bitmap)
+ PG_RETURN_INT32(0);
+ bitmask = 1;
+
+ 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
***************
*** 2963,2968 **** DESCR("adjust time with time zone precision");
--- 2963,2970 ----
DATA(insert OID = 2003 ( textanycat PGNSP PGUID 14 1 0 0 0 f f f f t f s 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 s 2 0 25 "2776 25" _null_ _null_ _null_ _null_ _null_ "select $1::pg_catalog.text || $2" _null_ _null_ _null_ ));
+ DATA(insert OID = 4400 ( num_nulls PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 1 0 23 "2276" "{2276}" "{v}" _null_ _null_ _null_ pg_num_nulls _null_ _null_ _null_ ));
+ DESCR("count the number of NULL input arguments");
DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 1 0 0 0 f f f f t f i s 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 s 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_num_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);
*** /dev/null
--- b/src/test/regress/expected/misc_functions.out
***************
*** 0 ****
--- 1,68 ----
+ --
+ -- num_nulls()
+ --
+ SELECT num_nulls();
+ ERROR: function num_nulls() does not exist
+ LINE 1: SELECT num_nulls();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT num_nulls(NULL);
+ num_nulls
+ -----------
+ 1
+ (1 row)
+
+ SELECT num_nulls('1');
+ num_nulls
+ -----------
+ 0
+ (1 row)
+
+ SELECT num_nulls(NULL::text);
+ num_nulls
+ -----------
+ 1
+ (1 row)
+
+ SELECT num_nulls(NULL::text, NULL::int);
+ num_nulls
+ -----------
+ 2
+ (1 row)
+
+ SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
+ num_nulls
+ -----------
+ 3
+ (1 row)
+
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ num_nulls
+ -----------
+
+ (1 row)
+
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+ num_nulls
+ -----------
+ 0
+ (1 row)
+
+ SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]);
+ num_nulls
+ -----------
+ 1
+ (1 row)
+
+ SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]);
+ num_nulls
+ -----------
+ 0
+ (1 row)
+
+ SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
+ num_nulls
+ -----------
+ 1
+ (1 row)
+
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 89,95 **** test: brin gin gist spgist privileges security_label collate matview lock replic
# ----------
# Another group of parallel tests
# ----------
! test: alter_generic alter_operator misc psql async dbsize
# rules cannot run concurrently with any test that creates a view
test: rules
--- 89,95 ----
# ----------
# Another group of parallel tests
# ----------
! test: alter_generic alter_operator misc psql async dbsize misc_functions
# rules cannot run concurrently with any test that creates a view
test: rules
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 118,123 **** test: misc
--- 118,124 ----
test: psql
test: async
test: dbsize
+ test: misc_functions
test: rules
test: select_views
test: portals_p2
*** /dev/null
--- b/src/test/regress/sql/misc_functions.sql
***************
*** 0 ****
--- 1,15 ----
+ --
+ -- num_nulls()
+ --
+
+ SELECT num_nulls();
+ SELECT num_nulls(NULL);
+ SELECT num_nulls('1');
+ SELECT num_nulls(NULL::text);
+ SELECT num_nulls(NULL::text, NULL::int);
+ SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+ SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]);
+ SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]);
+ SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
