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