count_nulls(VARIADIC "any")
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
Attachments:
count_nulls.patchtext/plain; charset=UTF-8; name=count_nulls.patch; x-mac-creator=0; x-mac-type=0Download
*** 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);
On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:
Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.
I'm kind of puzzled what kind of schema would need this.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Greg Stark wrote:
On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:
Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.I'm kind of puzzled what kind of schema would need this.
I've seen cases where you want some entity to be of either of some
number of types. In those cases you have nullable FKs in separate
columns, and only one of them can be non null.
The name count_nulls() suggest an aggregate function to me, though.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi
2015-08-12 19:18 GMT+02:00 Marko Tiikkaja <marko@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.
It is not bad idea
+1
Pavel
Show quoted text
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
The name count_nulls() suggest an aggregate function to me, though.
I thought the same.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-08-12 7:23 PM, Greg Stark wrote:
On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:
Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.I'm kind of puzzled what kind of schema would need this.
The first example I could find from our schema was specifying the URL
for a Remote Procedure Call. You can either specify a single request
URI, or you can specify the pieces: protocol, host, port, path. So the
constraints look roughly like this:
CHECK ((fulluri IS NULL) <> (protocol IS NULL)),
CHECK ((protocol IS NULL) = ALL(ARRAY[host IS NULL, port IS NULL,
path IS NULL]))
Obviously the second one would be much prettier with count_nulls().
The other example is an OOP inheritance-like schema where an object
could be one of any X number of types. You could write that:
CHECK ((a IS NULL)::int + (b IS NULL)::int + (c IS NULL)::int) = 1)
or just:
CHECK (count_nulls(a,b,c) = 1)
The first example could be redesigned with three tables, but that seems
like a cure worse than the disease.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-08-12 19:32 GMT+02:00 Peter Geoghegan <pg@heroku.com>:
On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:The name count_nulls() suggest an aggregate function to me, though.
I thought the same.
maybe nulls_count ?
we have regr_count already
Regards
Pavel
Show quoted text
--
Peter Geoghegan--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-08-12 7:35 PM, Pavel Stehule wrote:
maybe nulls_count ?
we have regr_count already
But that's an aggregate as well..
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-08-12 19:37 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
On 2015-08-12 7:35 PM, Pavel Stehule wrote:
maybe nulls_count ?
we have regr_count already
But that's an aggregate as well..
my mistake
Pavel
Show quoted text
.m
Peter Geoghegan <pg@heroku.com> writes:
On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:The name count_nulls() suggest an aggregate function to me, though.
I thought the same.
Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Geoghegan <pg@heroku.com> writes:
On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:The name count_nulls() suggest an aggregate function to me, though.
I thought the same.
Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.
nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...
David J.
On Thu, Aug 13, 2015 at 2:19 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Geoghegan <pg@heroku.com> writes:
On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:The name count_nulls() suggest an aggregate function to me, though.
I thought the same.
Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...
How about these:
nulls_rank() (the analogy being 0 <= "rank" <= "set size")
nnulls()
or just
nulls() (this one might be a bit confusing due to existing NULLS LAST/FIRST
syntax, though)
--
Alex
On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:
nnulls()
I think I'd prefer num_nulls() over that.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:
nnulls()
I think I'd prefer num_nulls() over that.
can be
what about similar twin function num_nonulls()?
Pavel
Show quoted text
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Aug 13, 2015 at 12:55 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:
nnulls()
I think I'd prefer num_nulls() over that.
can be
what about similar twin function num_nonulls()?
+1
On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:
nnulls()
I think I'd prefer num_nulls() over that.
can be
what about similar twin function num_nonulls()?
Yes. But I'm can't see any precedent for naming it like num_*... And if
anything, should it be num_nonnulls() then?
2015-08-13 9:47 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:
On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:
nnulls()
I think I'd prefer num_nulls() over that.
can be
what about similar twin function num_nonulls()?
Yes. But I'm can't see any precedent for naming it like num_*... And if
anything, should it be num_nonnulls() then?
it is detail - depends on final naming convention.
Hello,
Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.
.m
Attachments:
num_nulls_v2.patchtext/x-patch; name=num_nulls_v2.patchDownload
*** 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>
***************
*** 194,200 ****
linkend="functions-comparison-table">.
</para>
! <table id="functions-comparison-table">
<title>Comparison Operators</title>
<tgroup cols="2">
<thead>
--- 194,200 ----
linkend="functions-comparison-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">
*** 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));
Marko Tiikkaja <marko@joh.to> writes:
Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.
What's this do that "count(*) - count(x)" doesn't?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-11-21 06:06, Tom Lane wrote:
Marko Tiikkaja <marko@joh.to> writes:
Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.What's this do that "count(*) - count(x)" doesn't?
This is sort of a lateral version of count(x); the input is a list of
expressions rather than an expression executed over a bunch of input rows.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
num_nulls_v3.patchtext/x-patch; name=num_nulls_v3.patchDownload
*** 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));
On 11/20/15 11:12 PM, Marko Tiikkaja wrote:
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.
I thought there was going to be a not-null equivalent as well? I've
definitely wanted both variations in the past.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-11-21 06:52, Jim Nasby wrote:
On 11/20/15 11:12 PM, Marko Tiikkaja wrote:
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.I thought there was going to be a not-null equivalent as well? I've
definitely wanted both variations in the past.
I'm not sure that's necessary. It's quite simple to implement yourself
using the int - int operator.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/20/15 11:55 PM, Marko Tiikkaja wrote:
On 2015-11-21 06:52, Jim Nasby wrote:
On 11/20/15 11:12 PM, Marko Tiikkaja wrote:
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.I thought there was going to be a not-null equivalent as well? I've
definitely wanted both variations in the past.I'm not sure that's necessary. It's quite simple to implement yourself
using the int - int operator.
Only if you know how many columns there already are.
Or does this not work if you hand it a row?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-11-22 18:29, Jim Nasby wrote:
Only if you know how many columns there already are.
Or does this not work if you hand it a row?
It "works" in the sense that it tells you whether the row is NULL or
not. I.e. the answer will always be 0 or 1.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/22/15 11:34 AM, Marko Tiikkaja wrote:
On 2015-11-22 18:29, Jim Nasby wrote:
Only if you know how many columns there already are.
Or does this not work if you hand it a row?
It "works" in the sense that it tells you whether the row is NULL or
not. I.e. the answer will always be 0 or 1.
Hrm, I was hoping something like count_nulls(complex_type.*) would work.
I guess one could always create a wrapper function that does
count_not_nulls() anyway.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-11-22 21:17, Jim Nasby wrote:
On 11/22/15 11:34 AM, Marko Tiikkaja wrote:
On 2015-11-22 18:29, Jim Nasby wrote:
Only if you know how many columns there already are.
Or does this not work if you hand it a row?
It "works" in the sense that it tells you whether the row is NULL or
not. I.e. the answer will always be 0 or 1.Hrm, I was hoping something like count_nulls(complex_type.*) would work.
Nope:
=# select num_nulls((f).*) from (select '(1,2,3)'::foo) ss(f);
ERROR: row expansion via "*" is not supported here
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi
2015-08-12 19:18 GMT+02:00 Marko Tiikkaja <marko@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
Regards
Pavel
2016-01-03 21:37 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
Hi
2015-08-12 19:18 GMT+02:00 Marko Tiikkaja <marko@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
Show quoted text
Regards
Pavel
Attachments:
num_nulls_v4.patchtext/x-patch; charset=US-ASCII; name=num_nulls_v4.patchDownload
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));
+
+
On 1/3/16 2:37 PM, Pavel Stehule wrote:
+ /* num_nulls(VARIADIC NULL) is defined as NULL */ + if (PG_ARGISNULL(0)) + return false;
Could you add to the comment explaining why that's the desired behavior?
+ /* + * 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))));
Erm... is that really the way to verify that what you have is an array?
ISTM there should be a macro for that somewhere...
+ /* 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;
For brevity and example sake it'd probably be better to just use the
normal iterator, unless there's a serious speed difference?
In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...
Also, I don't think anything is testing multiples of whatever value...
how 'bout change the generate_series CASE statement to >40 instead of <>40?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi
2016-01-03 22:49 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 1/3/16 2:37 PM, Pavel Stehule wrote:
+ /* num_nulls(VARIADIC NULL) is defined as NULL */ + if (PG_ARGISNULL(0)) + return false;Could you add to the comment explaining why that's the desired behavior?
This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
situation is really equivalent of missing data and NULL is correct answer.
It should not be too clean in num_nulls, but when it is cleaner for
num_notnulls. And more, it is consistent with other variadic functions in
Postgres: see concat_internal and text_format.
+ /*
+ * 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))));Erm... is that really the way to verify that what you have is an array?
ISTM there should be a macro for that somewhere...
really, it is. It is used more time. Although I am not against some macro,
I don't think so it is necessary. The macro should not be too shorter than
this text.
+ /* 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;For brevity and example sake it'd probably be better to just use the
normal iterator, unless there's a serious speed difference?
The iterator does some memory allocations and some access to type cache.
Almost all work of iterator is useless for this case. This code is
developed by Marko, but I agree with this design. Using the iterator is big
gun for this case. I didn't any performance checks, but it should be
measurable for any varlena arrays.
Regards
Pavel
Show quoted text
In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...Also, I don't think anything is testing multiples of whatever value... how
'bout change the generate_series CASE statement to >40 instead of <>40?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
On 1/3/16 10:23 PM, Pavel Stehule wrote:
Hi
2016-01-03 22:49 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>>:On 1/3/16 2:37 PM, Pavel Stehule wrote:
+ /* num_nulls(VARIADIC NULL) is defined as NULL */ + if (PG_ARGISNULL(0)) + return false;Could you add to the comment explaining why that's the desired behavior?
This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
situation is really equivalent of missing data and NULL is correct
answer. It should not be too clean in num_nulls, but when it is cleaner
for num_notnulls. And more, it is consistent with other variadic
functions in Postgres: see concat_internal and text_format.
Makes sense, now that you explain it. Which is why I'm thinking it'd be
good to add that explanation to the comment... ;)
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0))));
Erm... is that really the way to verify that what you have is an
array? ISTM there should be a macro for that somewhere...really, it is. It is used more time. Although I am not against some
macro, I don't think so it is necessary. The macro should not be too
shorter than this text.
Well, if there's other stuff doing that... would be nice to refactor
that though.
For brevity and example sake it'd probably be better to just use the
normal iterator, unless there's a serious speed difference?The iterator does some memory allocations and some access to type cache.
Almost all work of iterator is useless for this case. This code is
developed by Marko, but I agree with this design. Using the iterator is
big gun for this case. I didn't any performance checks, but it should be
measurable for any varlena arrays.
Makes sense then.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi
2016-01-04 5:49 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 1/3/16 10:23 PM, Pavel Stehule wrote:
Hi
2016-01-03 22:49 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>>:On 1/3/16 2:37 PM, Pavel Stehule wrote:
+ /* num_nulls(VARIADIC NULL) is defined as NULL */ + if (PG_ARGISNULL(0)) + return false;Could you add to the comment explaining why that's the desired
behavior?This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
situation is really equivalent of missing data and NULL is correct
answer. It should not be too clean in num_nulls, but when it is cleaner
for num_notnulls. And more, it is consistent with other variadic
functions in Postgres: see concat_internal and text_format.Makes sense, now that you explain it. Which is why I'm thinking it'd be
good to add that explanation to the comment... ;)Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo,
0))));Erm... is that really the way to verify that what you have is an
array? ISTM there should be a macro for that somewhere...really, it is. It is used more time. Although I am not against some
macro, I don't think so it is necessary. The macro should not be too
shorter than this text.Well, if there's other stuff doing that... would be nice to refactor that
though.For brevity and example sake it'd probably be better to just use the
normal iterator, unless there's a serious speed difference?
The iterator does some memory allocations and some access to type cache.
Almost all work of iterator is useless for this case. This code is
developed by Marko, but I agree with this design. Using the iterator is
big gun for this case. I didn't any performance checks, but it should be
measurable for any varlena arrays.Makes sense then.
+ enhanced comment
+ rewritten regress tests
Regards
Pavel
Show quoted text
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
Attachments:
num_nulls_v5.patchtext/x-patch; charset=US-ASCII; name=num_nulls_v5.patchDownload
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..35810d1
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 43,48 ****
--- 43,160 ----
#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;
+
+ /*
+ * When parameter with packed variadic arguments is NULL, we
+ * cannot to identify number of variadic argumens (NULL
+ * or not NULL), then the correct result is NULL. This behave
+ * is consistent with other variadic functions - see concat_internal.
+ */
+ 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..2fc68fc
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum pg_ls_dir(PG_FUNCTION_ARGS)
*** 481,486 ****
--- 481,488 ----
extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
/* misc.c */
+ extern Datum pg_num_notnulls(PG_FUNCTION_ARGS);
+ 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 ...03d4c47
*** a/src/test/regress/expected/misc_functions.out
--- b/src/test/regress/expected/misc_functions.out
***************
*** 0 ****
--- 1,87 ----
+ --
+ -- num_nulls()
+ --
+ -- should fail, one or more arguments is required
+ 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_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.
+ -- special cases
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ num_notnulls
+ --------------
+
+ (1 row)
+
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ num_nulls
+ -----------
+
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+ num_nulls
+ -----------
+ 0
+ (1 row)
+
+ CREATE TABLE num_nulls_test(a int, b text, c numeric);
+ INSERT INTO num_nulls_test VALUES(NULL, NULL, NULL);
+ INSERT INTO num_nulls_test VALUES(10, NULL, NULL);
+ INSERT INTO num_nulls_test VALUES(10, 'Hello', NULL);
+ INSERT INTO num_nulls_test VALUES(10, 'Hello', 1.0);
+ SELECT num_notnulls(a, b, c), num_nulls(a, b, c) FROM num_nulls_test;
+ num_notnulls | num_nulls
+ --------------+-----------
+ 0 | 3
+ 1 | 2
+ 2 | 1
+ 3 | 0
+ (4 rows)
+
+ SELECT num_notnulls(VARIADIC ARRAY[a::text, b::text, c::text]),
+ num_nulls(VARIADIC ARRAY[a::text, b::text, c::text])
+ FROM num_nulls_test;
+ num_notnulls | num_nulls
+ --------------+-----------
+ 0 | 3
+ 1 | 2
+ 2 | 1
+ 3 | 0
+ (4 rows)
+
+ SELECT num_notnulls(VARIADIC ARRAY[a,c]), num_nulls(VARIADIC ARRAY[a,c])
+ FROM num_nulls_test;
+ num_notnulls | num_nulls
+ --------------+-----------
+ 0 | 2
+ 1 | 1
+ 1 | 1
+ 2 | 0
+ (4 rows)
+
+ SELECT num_notnulls(VARIADIC ARRAY(SELECT CASE WHEN i > 40 THEN i END FROM generate_series(1, 100) i));
+ num_notnulls
+ --------------
+ 60
+ (1 row)
+
+ SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i > 40 THEN i END FROM generate_series(1, 100) i));
+ num_nulls
+ -----------
+ 40
+ (1 row)
+
+ DROP TABLE num_nulls_test;
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 ...31d0738
*** a/src/test/regress/sql/misc_functions.sql
--- b/src/test/regress/sql/misc_functions.sql
***************
*** 0 ****
--- 1,32 ----
+ --
+ -- num_nulls()
+ --
+
+ -- should fail, one or more arguments is required
+ SELECT num_notnulls();
+ SELECT num_nulls();
+
+ -- special cases
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+
+ CREATE TABLE num_nulls_test(a int, b text, c numeric);
+ INSERT INTO num_nulls_test VALUES(NULL, NULL, NULL);
+ INSERT INTO num_nulls_test VALUES(10, NULL, NULL);
+ INSERT INTO num_nulls_test VALUES(10, 'Hello', NULL);
+ INSERT INTO num_nulls_test VALUES(10, 'Hello', 1.0);
+
+ SELECT num_notnulls(a, b, c), num_nulls(a, b, c) FROM num_nulls_test;
+ SELECT num_notnulls(VARIADIC ARRAY[a::text, b::text, c::text]),
+ num_nulls(VARIADIC ARRAY[a::text, b::text, c::text])
+ FROM num_nulls_test;
+
+ SELECT num_notnulls(VARIADIC ARRAY[a,c]), num_nulls(VARIADIC ARRAY[a,c])
+ FROM num_nulls_test;
+
+ SELECT num_notnulls(VARIADIC ARRAY(SELECT CASE WHEN i > 40 THEN i END FROM generate_series(1, 100) i));
+ SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i > 40 THEN i END FROM generate_series(1, 100) i));
+
+ DROP TABLE num_nulls_test;
On 03/01/16 22:49, Jim Nasby wrote:
In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...
I don't really see the point. "The size of the test" doesn't seem like
a worthwhile optimization target, unless the test scripts are somehow
really unnecessarily large.
Further, if you were developing code related to this, previously you
could just copy-paste the defective test case in order to easily
reproduce a problem. But now suddenly you need a ton of different setup.
I don't expect to really have a say in this, but I think the tests are
now worse than they were before.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2016-01-12 17:27 GMT+01:00 Marko Tiikkaja <marko@joh.to>:
On 03/01/16 22:49, Jim Nasby wrote:
In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...I don't really see the point. "The size of the test" doesn't seem like a
worthwhile optimization target, unless the test scripts are somehow really
unnecessarily large.Further, if you were developing code related to this, previously you could
just copy-paste the defective test case in order to easily reproduce a
problem. But now suddenly you need a ton of different setup.I don't expect to really have a say in this, but I think the tests are now
worse than they were before.
the form of regress tests is not pretty significant issue. Jim's design is
little bit transparent, Marko's is maybe little bit practical. Both has
sense from my opinion, and any hasn't significant advantage against other.
Regards
Pavel
Show quoted text
.m
Hi
2016-01-17 8:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
2016-01-12 17:27 GMT+01:00 Marko Tiikkaja <marko@joh.to>:
On 03/01/16 22:49, Jim Nasby wrote:
In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...I don't really see the point. "The size of the test" doesn't seem like a
worthwhile optimization target, unless the test scripts are somehow really
unnecessarily large.Further, if you were developing code related to this, previously you
could just copy-paste the defective test case in order to easily reproduce
a problem. But now suddenly you need a ton of different setup.I don't expect to really have a say in this, but I think the tests are
now worse than they were before.the form of regress tests is not pretty significant issue. Jim's design is
little bit transparent, Marko's is maybe little bit practical. Both has
sense from my opinion, and any hasn't significant advantage against other.
any possible agreement, how these tests should be designed?
simple patch, simple regress tests, so there are no reason for long waiting.
Regards
Pavel
Show quoted text
Regards
Pavel
.m
On 1/21/16 1:48 PM, Pavel Stehule wrote:
the form of regress tests is not pretty significant issue. Jim's
design is little bit transparent, Marko's is maybe little bit
practical. Both has sense from my opinion, and any hasn't
significant advantage against other.any possible agreement, how these tests should be designed?
simple patch, simple regress tests, so there are no reason for long waiting.
I don't really see how individual tests are more practical (you can
still cut and paste a table...), but since there's no strong consensus
either way I'd say it's up to you as author.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2016-01-22 13:34 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 1/21/16 1:48 PM, Pavel Stehule wrote:
the form of regress tests is not pretty significant issue. Jim's
design is little bit transparent, Marko's is maybe little bit
practical. Both has sense from my opinion, and any hasn't
significant advantage against other.any possible agreement, how these tests should be designed?
simple patch, simple regress tests, so there are no reason for long
waiting.I don't really see how individual tests are more practical (you can still
cut and paste a table...), but since there's no strong consensus either way
I'd say it's up to you as author.
Marco is a author of this patch, so - Marco, please, send final version of
this patch
Regards
Pavel
Show quoted text
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
On 25/01/16 19:57, Pavel Stehule wrote:
Marco is a author of this patch, so - Marco, please, send final version of
this patch
I don't really care about the tests. Can we not use the v5 patch
already in the thread? As far as I could tell there were no reviewer's
comments on it anymore.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2016-01-26 11:42 GMT+01:00 Marko Tiikkaja <marko@joh.to>:
On 25/01/16 19:57, Pavel Stehule wrote:
Marco is a author of this patch, so - Marco, please, send final version of
this patchI don't really care about the tests. Can we not use the v5 patch already
in the thread? As far as I could tell there were no reviewer's comments on
it anymore.
It was not my request, but I counted Jim as second reviewer.
So, I'll return back original regress tests. If I remember well, there are
no any other objection, so I'll mark this version as ready for commiter.
1. the patch is rebased against master
2. now warning or errors due compilation
3. all tests are passed
4. the code is simple without side effects and possible negative
performance impacts
6. there was not objections against the design
7. the iteration over null bitmap is used more times in our code, but this
is new special case. We don't need iterate over array elements, so we
should not to use existing array iterators.
I'll mark this patch as ready for commiter
Regards
Pavel
Show quoted text
.m
Attachments:
num_nulls_v6.patchtext/x-patch; charset=US-ASCII; name=num_nulls_v6.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 9c143b2..23c933f
*** 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
*** 10389,10395 ****
</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">.
--- 10426,10432 ----
</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..35810d1
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 43,48 ****
--- 43,160 ----
#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;
+
+ /*
+ * When parameter with packed variadic arguments is NULL, we
+ * cannot to identify number of variadic argumens (NULL
+ * or not NULL), then the correct result is NULL. This behave
+ * is consistent with other variadic functions - see concat_internal.
+ */
+ 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 79e92ff..3803763
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("adjust time with time zone precis
*** 2884,2889 ****
--- 2884,2893 ----
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 c2e529f..b31b6a3
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum pg_ls_dir(PG_FUNCTION_ARGS)
*** 489,494 ****
--- 489,496 ----
extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
/* misc.c */
+ extern Datum pg_num_notnulls(PG_FUNCTION_ARGS);
+ 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 ...e3652bb
*** a/src/test/regress/expected/misc_functions.out
--- b/src/test/regress/expected/misc_functions.out
***************
*** 0 ****
--- 1,135 ----
+ --
+ -- num_nulls()
+ --
+ 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 '{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(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 '{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)
+
+ -- special cases
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ num_notnulls
+ --------------
+
+ (1 row)
+
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ num_notnulls
+ --------------
+ 0
+ (1 row)
+
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ num_nulls
+ -----------
+
+ (1 row)
+
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+ num_nulls
+ -----------
+ 0
+ (1 row)
+
+ -- should fail, one or more arguments is required
+ 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_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.
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 ...bc3a185
*** a/src/test/regress/sql/misc_functions.sql
--- b/src/test/regress/sql/misc_functions.sql
***************
*** 0 ****
--- 1,31 ----
+ --
+ -- num_nulls()
+ --
+
+ 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 '{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(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 '{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));
+
+ -- special cases
+ SELECT num_notnulls(VARIADIC NULL::text[]);
+ SELECT num_notnulls(VARIADIC '{}'::int[]);
+ SELECT num_nulls(VARIADIC NULL::text[]);
+ SELECT num_nulls(VARIADIC '{}'::int[]);
+
+ -- should fail, one or more arguments is required
+ SELECT num_notnulls();
+ SELECT num_nulls();
Pavel Stehule <pavel.stehule@gmail.com> writes:
[ num_nulls_v6.patch ]
I started looking through this. It seems generally okay, but I'm not
very pleased with the function name "num_notnulls". I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
[ num_nulls_v6.patch ]
I started looking through this. It seems generally okay, but I'm not
very pleased with the function name "num_notnulls". I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.
Not hearing any complaints, I pushed it with that change and some other
cosmetic adjustments.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Dne 5. 2. 2016 1:33 napsal uživatel "Tom Lane" <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
[ num_nulls_v6.patch ]
I started looking through this. It seems generally okay, but I'm not
very pleased with the function name "num_notnulls". I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.
I have no problem with it.
Regards
Pavel
Show quoted text
regards, tom lane
On 2016-02-05 05:06, Tom Lane wrote:
I wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
[ num_nulls_v6.patch ]
I started looking through this. It seems generally okay, but I'm not
very pleased with the function name "num_notnulls". I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.Not hearing any complaints, I pushed it with that change and some other
cosmetic adjustments.
Thanks Tom and Pavel and everyone who provided feedback.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Feb 5, 2016 at 5:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
[ num_nulls_v6.patch ]
I started looking through this. It seems generally okay, but I'm not
very pleased with the function name "num_notnulls". I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.Not hearing any complaints, I pushed it with that change and some other
cosmetic adjustments.
Would num_values be a better name than num_nonnulls?
--
Thomas Munro
http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Thomas Munro <thomas.munro@enterprisedb.com> writes:
Would num_values be a better name than num_nonnulls?
If "value" is a term that excludes null values, it's news to me.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Feb 9, 2016 at 9:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Thomas Munro <thomas.munro@enterprisedb.com> writes:
Would num_values be a better name than num_nonnulls?
If "value" is a term that excludes null values, it's news to me.
Ah, right, I was thinking of null as the absence of a value. But in
fact it is a special value that indicates the absence of a "data
value". And num_data_values doesn't sound great.
--
Thomas Munro
http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers