Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6819514

Browse files
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 thatexactly one of a,b,c isn't NULL. The functions are variadic, so theycan also be pressed into service to count the number of null or nonnullelements in an array.Marko Tiikkaja, reviewed by Pavel Stehule
1 parentd0cd7bd commit6819514

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>ComparisonFunctions andOperators</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,asshown 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
#defineatooid(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+
staticbool
54+
count_nulls(FunctionCallInfofcinfo,
55+
int32*nargs,int32*nulls)
56+
{
57+
int32count=0;
58+
inti;
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+
intndims,
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+
intbitmask=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+
int32nargs,
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+
int32nargs,
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-
#defineCATALOG_VERSION_NO201602031
56+
#defineCATALOG_VERSION_NO201602041
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
externDatumpg_ls_dir_1arg(PG_FUNCTION_ARGS);
491491

492492
/* misc.c */
493+
externDatumpg_num_nulls(PG_FUNCTION_ARGS);
494+
externDatumpg_num_nonnulls(PG_FUNCTION_ARGS);
493495
externDatumcurrent_database(PG_FUNCTION_ARGS);
494496
externDatumcurrent_query(PG_FUNCTION_ARGS);
495497
externDatumpg_cancel_backend(PG_FUNCTION_ARGS);
@@ -823,7 +825,7 @@ extern Datum textoverlay_no_len(PG_FUNCTION_ARGS);
823825
externDatumname_text(PG_FUNCTION_ARGS);
824826
externDatumtext_name(PG_FUNCTION_ARGS);
825827
externintvarstr_cmp(char*arg1,intlen1,char*arg2,intlen2,Oidcollid);
826-
externvoidvarstr_sortsupport(SortSupportssup,Oidcollid,boolbpchar);
828+
externvoidvarstr_sortsupport(SortSupportssup,Oidcollid,boolbpchar);
827829
externintvarstr_levenshtein(constchar*source,intslen,
828830
constchar*target,inttlen,
829831
intins_c,intdel_c,intsub_c,
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)
[8]ページ先頭
©2009-2025 Movatter.jp