1--
2-- num_nulls()
3--
4SELECT num_nonnulls(NULL);
5 num_nonnulls
6--------------
7            0
8(1 row)
9
10SELECT num_nonnulls('1');
11 num_nonnulls
12--------------
13            1
14(1 row)
15
16SELECT num_nonnulls(NULL::text);
17 num_nonnulls
18--------------
19            0
20(1 row)
21
22SELECT num_nonnulls(NULL::text, NULL::int);
23 num_nonnulls
24--------------
25            0
26(1 row)
27
28SELECT num_nonnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
29 num_nonnulls
30--------------
31            4
32(1 row)
33
34SELECT num_nonnulls(VARIADIC '{1,2,NULL,3}'::int[]);
35 num_nonnulls
36--------------
37            3
38(1 row)
39
40SELECT num_nonnulls(VARIADIC '{"1","2","3","4"}'::text[]);
41 num_nonnulls
42--------------
43            4
44(1 row)
45
46SELECT 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
52SELECT num_nulls(NULL);
53 num_nulls
54-----------
55         1
56(1 row)
57
58SELECT num_nulls('1');
59 num_nulls
60-----------
61         0
62(1 row)
63
64SELECT num_nulls(NULL::text);
65 num_nulls
66-----------
67         1
68(1 row)
69
70SELECT num_nulls(NULL::text, NULL::int);
71 num_nulls
72-----------
73         2
74(1 row)
75
76SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
77 num_nulls
78-----------
79         3
80(1 row)
81
82SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]);
83 num_nulls
84-----------
85         1
86(1 row)
87
88SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]);
89 num_nulls
90-----------
91         0
92(1 row)
93
94SELECT 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
101SELECT num_nonnulls(VARIADIC NULL::text[]);
102 num_nonnulls
103--------------
104
105(1 row)
106
107SELECT num_nonnulls(VARIADIC '{}'::int[]);
108 num_nonnulls
109--------------
110            0
111(1 row)
112
113SELECT num_nulls(VARIADIC NULL::text[]);
114 num_nulls
115-----------
116
117(1 row)
118
119SELECT num_nulls(VARIADIC '{}'::int[]);
120 num_nulls
121-----------
122         0
123(1 row)
124
125-- should fail, one or more arguments is required
126SELECT num_nonnulls();
127ERROR:  function num_nonnulls() does not exist
128LINE 1: SELECT num_nonnulls();
129               ^
130HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
131SELECT num_nulls();
132ERROR:  function num_nulls() does not exist
133LINE 1: SELECT num_nulls();
134               ^
135HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
136--
137-- Test some built-in SRFs
138--
139-- The outputs of these are variable, so we can't just print their results
140-- directly, but we can at least verify that the code doesn't fail.
141--
142select setting as segbsize
143from pg_settings where name = 'wal_segment_size'
144\gset
145select setting as bsize
146from pg_settings where name = 'block_size'
147\gset
148select count(*) > 0 as ok from pg_ls_waldir();
149 ok
150----
151 t
152(1 row)
153
154-- Test ProjectSet as well as FunctionScan
155select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
156 ok
157----
158 t
159(1 row)
160
161-- Test not-run-to-completion cases.
162select * from pg_ls_waldir() limit 0;
163 name | size | modification
164------+------+--------------
165(0 rows)
166
167select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
168 ok
169----
170 t
171(1 row)
172
173select (w).size = :segbsize * :bsize as ok
174from (select pg_ls_waldir() w) ss where length((w).name) = 24 limit 1;
175 ok
176----
177 t
178(1 row)
179
180select * from (select pg_ls_dir('.') a) a where a = 'base' limit 1;
181  a
182------
183 base
184(1 row)
185
186select * from (select (pg_timezone_names()).name) ptn where name='UTC' limit 1;
187 name
188------
189 UTC
190(1 row)
191
192select count(*) > 0 from
193  (select pg_tablespace_databases(oid) as pts from pg_tablespace
194   where spcname = 'pg_default') pts
195  join pg_database db on pts.pts = db.oid;
196 ?column?
197----------
198 t
199(1 row)
200
201