Skip to content

Commit 6819514

Browse files
tglsfdctglsfdc
committed
Add num_nulls() and num_nonnulls() to count NULL arguments.
An example use-case is "CHECK(num_nonnulls(a,b,c) = 1)" to assert that exactly one of a,b,c isn't NULL. The functions are variadic, so they can also be pressed into service to count the number of null or nonnull elements in an array. Marko Tiikkaja, reviewed by Pavel Stehule
1 parent d0cd7bd commit 6819514

File tree

9 files changed

+347
-8
lines changed

9 files changed

+347
-8
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 48 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -182,19 +182,19 @@
182182
</sect1>
183183

184184
<sect1 id="functions-comparison">
185-
<title>Comparison Operators</title>
185+
<title>Comparison Functions and Operators</title>
186186

187187
<indexterm zone="functions-comparison">
188188
<primary>comparison</primary>
189189
<secondary>operators</secondary>
190190
</indexterm>
191191

192192
<para>
193-
The usual comparison operators are available, shown in <xref
194-
linkend="functions-comparison-table">.
193+
The usual comparison operators are available, as shown in <xref
194+
linkend="functions-comparison-op-table">.
195195
</para>
196196

197-
<table id="functions-comparison-table">
197+
<table id="functions-comparison-op-table">
198198
<title>Comparison Operators</title>
199199
<tgroup cols="2">
200200
<thead>
@@ -437,6 +437,49 @@
437437
</para>
438438
-->
439439

440+
<para>
441+
Some comparison-related functions are also available, as shown in <xref
442+
linkend="functions-comparison-func-table">.
443+
</para>
444+
445+
<table id="functions-comparison-func-table">
446+
<title>Comparison Functions</title>
447+
<tgroup cols="4">
448+
<thead>
449+
<row>
450+
<entry>Function</entry>
451+
<entry>Description</entry>
452+
<entry>Example</entry>
453+
<entry>Example Result</entry>
454+
</row>
455+
</thead>
456+
<tbody>
457+
<row>
458+
<entry>
459+
<indexterm>
460+
<primary>num_nonnulls</primary>
461+
</indexterm>
462+
<literal>num_nonnulls(VARIADIC "any")</literal>
463+
</entry>
464+
<entry>returns the number of non-NULL arguments</entry>
465+
<entry><literal>num_nonnulls(1, NULL, 2)</literal></entry>
466+
<entry><literal>2</literal></entry>
467+
</row>
468+
<row>
469+
<entry>
470+
<indexterm>
471+
<primary>num_nulls</primary>
472+
</indexterm>
473+
<literal>num_nulls(VARIADIC "any")</literal>
474+
</entry>
475+
<entry>returns the number of NULL arguments</entry>
476+
<entry><literal>num_nulls(1, NULL, 2)</literal></entry>
477+
<entry><literal>1</literal></entry>
478+
</row>
479+
</tbody>
480+
</tgroup>
481+
</table>
482+
440483
</sect1>
441484

442485
<sect1 id="functions-math">
@@ -10389,7 +10432,7 @@ table2-mapping
1038910432
</note>
1039010433
<para>
1039110434
The standard comparison operators shown in <xref
10392-
linkend="functions-comparison-table"> are available for
10435+
linkend="functions-comparison-op-table"> are available for
1039310436
<type>jsonb</type>, but not for <type>json</type>. They follow the
1039410437
ordering rules for B-tree operations outlined at <xref
1039510438
linkend="json-indexing">.

‎src/backend/utils/adt/misc.c

Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,127 @@
4444
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
4545

4646

47+
/*
48+
* Common subroutine for num_nulls() and num_nonnulls().
49+
* Returns TRUE if successful, FALSE if function should return NULL.
50+
* If successful, total argument count and number of nulls are
51+
* returned into *nargs and *nulls.
52+
*/
53+
static bool
54+
count_nulls(FunctionCallInfo fcinfo,
55+
int32 *nargs, int32 *nulls)
56+
{
57+
int32 count = 0;
58+
int i;
59+
60+
/* Did we get a VARIADIC array argument, or separate arguments? */
61+
if (get_fn_expr_variadic(fcinfo->flinfo))
62+
{
63+
ArrayType *arr;
64+
int ndims,
65+
nitems,
66+
*dims;
67+
bits8 *bitmap;
68+
69+
Assert(PG_NARGS() == 1);
70+
71+
/*
72+
* If we get a null as VARIADIC array argument, we can't say anything
73+
* useful about the number of elements, so return NULL. This behavior
74+
* is consistent with other variadic functions - see concat_internal.
75+
*/
76+
if (PG_ARGISNULL(0))
77+
return false;
78+
79+
/*
80+
* Non-null argument had better be an array. We assume that any call
81+
* context that could let get_fn_expr_variadic return true will have
82+
* checked that a VARIADIC-labeled parameter actually is an array. So
83+
* it should be okay to just Assert that it's an array rather than
84+
* doing a full-fledged error check.
85+
*/
86+
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0))));
87+
88+
/* OK, safe to fetch the array value */
89+
arr = PG_GETARG_ARRAYTYPE_P(0);
90+
91+
/* Count the array elements */
92+
ndims = ARR_NDIM(arr);
93+
dims = ARR_DIMS(arr);
94+
nitems = ArrayGetNItems(ndims, dims);
95+
96+
/* Count those that are NULL */
97+
bitmap = ARR_NULLBITMAP(arr);
98+
if (bitmap)
99+
{
100+
int bitmask = 1;
101+
102+
for (i = 0; i < nitems; i++)
103+
{
104+
if ((*bitmap & bitmask) == 0)
105+
count++;
106+
107+
bitmask <<= 1;
108+
if (bitmask == 0x100)
109+
{
110+
bitmap++;
111+
bitmask = 1;
112+
}
113+
}
114+
}
115+
116+
*nargs = nitems;
117+
*nulls = count;
118+
}
119+
else
120+
{
121+
/* Separate arguments, so just count 'em */
122+
for (i = 0; i < PG_NARGS(); i++)
123+
{
124+
if (PG_ARGISNULL(i))
125+
count++;
126+
}
127+
128+
*nargs = PG_NARGS();
129+
*nulls = count;
130+
}
131+
132+
return true;
133+
}
134+
135+
/*
136+
* num_nulls()
137+
* Count the number of NULL arguments
138+
*/
139+
Datum
140+
pg_num_nulls(PG_FUNCTION_ARGS)
141+
{
142+
int32 nargs,
143+
nulls;
144+
145+
if (!count_nulls(fcinfo, &nargs, &nulls))
146+
PG_RETURN_NULL();
147+
148+
PG_RETURN_INT32(nulls);
149+
}
150+
151+
/*
152+
* num_nonnulls()
153+
* Count the number of non-NULL arguments
154+
*/
155+
Datum
156+
pg_num_nonnulls(PG_FUNCTION_ARGS)
157+
{
158+
int32 nargs,
159+
nulls;
160+
161+
if (!count_nulls(fcinfo, &nargs, &nulls))
162+
PG_RETURN_NULL();
163+
164+
PG_RETURN_INT32(nargs - nulls);
165+
}
166+
167+
47168
/*
48169
* current_database()
49170
* Expose the current database to the user

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201602031
56+
#define CATALOG_VERSION_NO 201602041
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -686,6 +686,12 @@ DATA(insert OID = 422 ( hashinet PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0
686686
DESCR("hash");
687687
DATA(insert OID = 432 ( hash_numeric PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 23 "1700" _null_ _null_ _null_ _null_ _null_ hash_numeric _null_ _null_ _null_ ));
688688
DESCR("hash");
689+
690+
DATA(insert OID = 438 ( 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_ ));
691+
DESCR("count the number of NULL arguments");
692+
DATA(insert OID = 440 ( num_nonnulls 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_nonnulls _null_ _null_ _null_ ));
693+
DESCR("count the number of non-NULL arguments");
694+
689695
DATA(insert OID = 458 ( text_larger PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ text_larger _null_ _null_ _null_ ));
690696
DESCR("larger of two");
691697
DATA(insert OID = 459 ( text_smaller PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ text_smaller _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -490,6 +490,8 @@ extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
490490
extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
491491

492492
/* misc.c */
493+
extern Datum pg_num_nulls(PG_FUNCTION_ARGS);
494+
extern Datum pg_num_nonnulls(PG_FUNCTION_ARGS);
493495
extern Datum current_database(PG_FUNCTION_ARGS);
494496
extern Datum current_query(PG_FUNCTION_ARGS);
495497
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
@@ -823,7 +825,7 @@ extern Datum textoverlay_no_len(PG_FUNCTION_ARGS);
823825
extern Datum name_text(PG_FUNCTION_ARGS);
824826
extern Datum text_name(PG_FUNCTION_ARGS);
825827
extern int varstr_cmp(char *arg1, int len1, char *arg2, int len2, Oid collid);
826-
extern void varstr_sortsupport(SortSupport ssup, Oid collid, bool bpchar);
828+
extern void varstr_sortsupport(SortSupport ssup, Oid collid, bool bpchar);
827829
extern int varstr_levenshtein(const char *source, int slen,
828830
const char *target, int tlen,
829831
int ins_c, int del_c, int sub_c,

‎src/test/regress/expected/misc_functions.out

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,135 @@
1+
--
2+
-- num_nulls()
3+
--
4+
SELECT num_nonnulls(NULL);
5+
num_nonnulls
6+
--------------
7+
0
8+
(1 row)
9+
10+
SELECT num_nonnulls('1');
11+
num_nonnulls
12+
--------------
13+
1
14+
(1 row)
15+
16+
SELECT num_nonnulls(NULL::text);
17+
num_nonnulls
18+
--------------
19+
0
20+
(1 row)
21+
22+
SELECT num_nonnulls(NULL::text, NULL::int);
23+
num_nonnulls
24+
--------------
25+
0
26+
(1 row)
27+
28+
SELECT num_nonnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
29+
num_nonnulls
30+
--------------
31+
4
32+
(1 row)
33+
34+
SELECT num_nonnulls(VARIADIC '{1,2,NULL,3}'::int[]);
35+
num_nonnulls
36+
--------------
37+
3
38+
(1 row)
39+
40+
SELECT num_nonnulls(VARIADIC '{"1","2","3","4"}'::text[]);
41+
num_nonnulls
42+
--------------
43+
4
44+
(1 row)
45+
46+
SELECT num_nonnulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
47+
num_nonnulls
48+
--------------
49+
99
50+
(1 row)
51+
52+
SELECT num_nulls(NULL);
53+
num_nulls
54+
-----------
55+
1
56+
(1 row)
57+
58+
SELECT num_nulls('1');
59+
num_nulls
60+
-----------
61+
0
62+
(1 row)
63+
64+
SELECT num_nulls(NULL::text);
65+
num_nulls
66+
-----------
67+
1
68+
(1 row)
69+
70+
SELECT num_nulls(NULL::text, NULL::int);
71+
num_nulls
72+
-----------
73+
2
74+
(1 row)
75+
76+
SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
77+
num_nulls
78+
-----------
79+
3
80+
(1 row)
81+
82+
SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]);
83+
num_nulls
84+
-----------
85+
1
86+
(1 row)
87+
88+
SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]);
89+
num_nulls
90+
-----------
91+
0
92+
(1 row)
93+
94+
SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
95+
num_nulls
96+
-----------
97+
1
98+
(1 row)
99+
100+
-- special cases
101+
SELECT num_nonnulls(VARIADIC NULL::text[]);
102+
num_nonnulls
103+
--------------
104+
105+
(1 row)
106+
107+
SELECT num_nonnulls(VARIADIC '{}'::int[]);
108+
num_nonnulls
109+
--------------
110+
0
111+
(1 row)
112+
113+
SELECT num_nulls(VARIADIC NULL::text[]);
114+
num_nulls
115+
-----------
116+
117+
(1 row)
118+
119+
SELECT num_nulls(VARIADIC '{}'::int[]);
120+
num_nulls
121+
-----------
122+
0
123+
(1 row)
124+
125+
-- should fail, one or more arguments is required
126+
SELECT num_nonnulls();
127+
ERROR: function num_nonnulls() does not exist
128+
LINE 1: SELECT num_nonnulls();
129+
^
130+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
131+
SELECT num_nulls();
132+
ERROR: function num_nulls() does not exist
133+
LINE 1: SELECT num_nulls();
134+
^
135+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,7 +89,7 @@ test: brin gin gist spgist privileges security_label collate matview lock replic
8989
# ----------
9090
# Another group of parallel tests
9191
# ----------
92-
test: alter_generic alter_operator misc psql async dbsize
92+
test: alter_generic alter_operator misc psql async dbsize misc_functions
9393

9494
# rules cannot run concurrently with any test that creates a view
9595
test: rules

0 commit comments

Comments
 (0)