Hi
2016-01-04 5:49 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
> On 1/3/16 10:23 PM, Pavel Stehule wrote:
>
>> Hi
>>
>> 2016-01-03 22:49 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com
>> <mailto:jim.na...@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
> --
> 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
>
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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
