2016-01-03 21:37 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
> Hi
>
>
> 2015-08-12 19:18 GMT+02:00 Marko Tiikkaja <ma...@joh.to>:
>
>> 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
>>
>
> I am sending updated version - support num_nulls and num_notnulls
>
and patch
>
> Regards
>
> Pavel
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 8ef9fce..fd7890e
*** 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,479 ----
</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_notnulls</primary>
+ </indexterm>
+ <literal>num_notnulls(VARIADIC "any")</literal>
+ </entry>
+ <entry>Returns the number of not NULL input arguments</entry>
+ <entry><literal>num_nulls(1, NULL, 2)</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <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">
*************** table2-mapping
*** 10307,10313 ****
</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">.
--- 10344,10350 ----
</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">.
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
new file mode 100644
index 6a306f3..873d8f6
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 43,48 ****
--- 43,155 ----
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
+ /*
+ * Collect info about NULL arguments. Returns true when result values
+ * are valid.
+ */
+ static bool
+ count_nulls(FunctionCallInfo fcinfo,
+ int32 *nargs, int32 *nulls)
+ {
+ int32 count = 0;
+ int i;
+
+ if (get_fn_expr_variadic(fcinfo->flinfo))
+ {
+ ArrayType *arr;
+ int ndims, nitems, *dims;
+ bits8 *bitmap;
+ int bitmask;
+
+ /* num_nulls(VARIADIC NULL) is defined as NULL */
+ if (PG_ARGISNULL(0))
+ return false;
+
+ /*
+ * 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)
+ {
+ bitmask = 1;
+
+ for (i = 0; i < nitems; i++)
+ {
+ if ((*bitmap & bitmask) == 0)
+ count++;
+
+ bitmask <<= 1;
+ if (bitmask == 0x100)
+ {
+ bitmap++;
+ bitmask = 1;
+ }
+ }
+ }
+
+ *nargs = nitems;
+ *nulls = count;
+ }
+ else
+ {
+ for (i = 0; i < PG_NARGS(); i++)
+ {
+ if (PG_ARGISNULL(i))
+ count++;
+ }
+
+ *nargs = PG_NARGS();
+ *nulls = count;
+ }
+
+ return true;
+ }
+
+ /*
+ * num_nulls()
+ * Count the number of NULL input arguments
+ */
+ Datum
+ pg_num_nulls(PG_FUNCTION_ARGS)
+ {
+ int32 nargs,
+ nulls;
+
+ if (!count_nulls(fcinfo, &nargs, &nulls))
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT32(nulls);
+ }
+
+ /*
+ * num_notnulls()
+ * Count the number of not NULL input arguments
+ */
+ Datum
+ pg_num_notnulls(PG_FUNCTION_ARGS)
+ {
+ int32 nargs,
+ nulls;
+
+ if (!count_nulls(fcinfo, &nargs, &nulls))
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT32(nargs - nulls);
+ }
+
/*
* current_database()
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index e5d6c77..ad1a70c
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("adjust time with time zone precis
*** 2963,2968 ****
--- 2963,2972 ----
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 = 4401 ( num_notnulls 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_notnulls _null_ _null_ _null_ ));
+ DESCR("count the number of not 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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index bbaa2ce..dac296f
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum pg_ls_dir(PG_FUNCTION_ARGS)
*** 481,486 ****
--- 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);
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
new file mode 100644
index ...5138fd1
*** a/src/test/regress/expected/misc_functions.out
--- b/src/test/regress/expected/misc_functions.out
***************
*** 0 ****
--- 1,133 ----
+ --
+ -- num_nulls()
+ --
+ SELECT num_notnulls();
+ ERROR: function num_notnulls() does not exist
+ LINE 1: SELECT num_notnulls();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT num_notnulls(NULL);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_notnulls('1');
+ num_notnulls
+ --------------
+ 1
+ (1 row)
+
+ SELECT num_notnulls(NULL::text);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_notnulls(NULL::text, NULL::int);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_notnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
+ num_notnulls
+ --------------
+ 4
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ num_notnulls
+ --------------
+
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC '{1,2,NULL,3}'::int[]);
+ num_notnulls
+ --------------
+ 3
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC '{"1","2","3","4"}'::text[]);
+ num_notnulls
+ --------------
+ 4
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
+ num_notnulls
+ --------------
+ 99
+ (1 row)
+
+ 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)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
new file mode 100644
index b1bc7c7..bec0316
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
*************** test: brin gin gist spgist privileges se
*** 89,95 ****
# ----------
# 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
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
new file mode 100644
index ade9ef1..7e9b319
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
*************** test: misc
*** 119,124 ****
--- 119,125 ----
test: psql
test: async
test: dbsize
+ test: misc_functions
test: rules
test: select_views
test: portals_p2
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
new file mode 100644
index ...11f713d
*** a/src/test/regress/sql/misc_functions.sql
--- b/src/test/regress/sql/misc_functions.sql
***************
*** 0 ****
--- 1,29 ----
+ --
+ -- num_nulls()
+ --
+
+ SELECT num_notnulls();
+ SELECT num_notnulls(NULL);
+ SELECT num_notnulls('1');
+ SELECT num_notnulls(NULL::text);
+ SELECT num_notnulls(NULL::text, NULL::int);
+ SELECT num_notnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ SELECT num_notnulls(VARIADIC '{1,2,NULL,3}'::int[]);
+ SELECT num_notnulls(VARIADIC '{"1","2","3","4"}'::text[]);
+ SELECT num_notnulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
+
+ 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
