1-- 2-- num_nulls() 3-- 4 5SELECT num_nonnulls(NULL); 6SELECT num_nonnulls('1'); 7SELECT num_nonnulls(NULL::text); 8SELECT num_nonnulls(NULL::text, NULL::int); 9SELECT num_nonnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL); 10SELECT num_nonnulls(VARIADIC '{1,2,NULL,3}'::int[]); 11SELECT num_nonnulls(VARIADIC '{"1","2","3","4"}'::text[]); 12SELECT num_nonnulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i)); 13 14SELECT num_nulls(NULL); 15SELECT num_nulls('1'); 16SELECT num_nulls(NULL::text); 17SELECT num_nulls(NULL::text, NULL::int); 18SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL); 19SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]); 20SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]); 21SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i)); 22 23-- special cases 24SELECT num_nonnulls(VARIADIC NULL::text[]); 25SELECT num_nonnulls(VARIADIC '{}'::int[]); 26SELECT num_nulls(VARIADIC NULL::text[]); 27SELECT num_nulls(VARIADIC '{}'::int[]); 28 29-- should fail, one or more arguments is required 30SELECT num_nonnulls(); 31SELECT num_nulls(); 32 33-- 34-- pg_log_backend_memory_contexts() 35-- 36-- Memory contexts are logged and they are not returned to the function. 37-- Furthermore, their contents can vary depending on the timing. However, 38-- we can at least verify that the code doesn't fail. 39-- 40SELECT * FROM pg_log_backend_memory_contexts(pg_backend_pid()); 41 42-- 43-- Test some built-in SRFs 44-- 45-- The outputs of these are variable, so we can't just print their results 46-- directly, but we can at least verify that the code doesn't fail. 47-- 48select setting as segsize 49from pg_settings where name = 'wal_segment_size' 50\gset 51 52select count(*) > 0 as ok from pg_ls_waldir(); 53-- Test ProjectSet as well as FunctionScan 54select count(*) > 0 as ok from (select pg_ls_waldir()) ss; 55-- Test not-run-to-completion cases. 56select * from pg_ls_waldir() limit 0; 57select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss; 58select (w).size = :segsize as ok 59from (select pg_ls_waldir() w) ss where length((w).name) = 24 limit 1; 60 61select count(*) >= 0 as ok from pg_ls_archive_statusdir(); 62 63select * from (select pg_ls_dir('.') a) a where a = 'base' limit 1; 64 65select * from (select (pg_timezone_names()).name) ptn where name='UTC' limit 1; 66 67select count(*) > 0 from 68 (select pg_tablespace_databases(oid) as pts from pg_tablespace 69 where spcname = 'pg_default') pts 70 join pg_database db on pts.pts = db.oid; 71 72-- 73-- Test adding a support function to a subject function 74-- 75 76CREATE FUNCTION my_int_eq(int, int) RETURNS bool 77 LANGUAGE internal STRICT IMMUTABLE PARALLEL SAFE 78 AS $$int4eq$$; 79 80-- By default, planner does not think that's selective 81EXPLAIN (COSTS OFF) 82SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1 83WHERE my_int_eq(a.unique2, 42); 84 85-- With support function that knows it's int4eq, we get a different plan 86ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func; 87 88EXPLAIN (COSTS OFF) 89SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1 90WHERE my_int_eq(a.unique2, 42); 91 92-- Also test non-default rowcount estimate 93CREATE FUNCTION my_gen_series(int, int) RETURNS SETOF integer 94 LANGUAGE internal STRICT IMMUTABLE PARALLEL SAFE 95 AS $$generate_series_int4$$ 96 SUPPORT test_support_func; 97 98EXPLAIN (COSTS OFF) 99SELECT * FROM tenk1 a JOIN my_gen_series(1,1000) g ON a.unique1 = g; 100 101EXPLAIN (COSTS OFF) 102SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g; 103