1-- 2-- expression evaluation tests that don't fit into a more specific file 3-- 4 5-- 6-- Tests for SQLVAlueFunction 7-- 8 9 10-- current_date (always matches because of transactional behaviour) 11SELECT date(now())::text = current_date::text; 12 13 14-- current_time / localtime 15SELECT now()::timetz::text = current_time::text; 16SELECT now()::timetz(4)::text = current_time(4)::text; 17SELECT now()::time::text = localtime::text; 18SELECT now()::time(3)::text = localtime(3)::text; 19 20-- current_timestamp / localtimestamp (always matches because of transactional behaviour) 21SELECT current_timestamp = NOW(); 22-- precision 23SELECT length(current_timestamp::text) >= length(current_timestamp(0)::text); 24-- localtimestamp 25SELECT now()::timestamp::text = localtimestamp::text; 26 27-- current_role/user/user is tested in rolnames.sql 28 29-- current database / catalog 30SELECT current_catalog = current_database(); 31 32-- current_schema 33SELECT current_schema; 34SET search_path = 'notme'; 35SELECT current_schema; 36SET search_path = 'pg_catalog'; 37SELECT current_schema; 38RESET search_path; 39 40 41-- 42-- Tests for BETWEEN 43-- 44 45explain (costs off) 46select count(*) from date_tbl 47 where f1 between '1997-01-01' and '1998-01-01'; 48select count(*) from date_tbl 49 where f1 between '1997-01-01' and '1998-01-01'; 50 51explain (costs off) 52select count(*) from date_tbl 53 where f1 not between '1997-01-01' and '1998-01-01'; 54select count(*) from date_tbl 55 where f1 not between '1997-01-01' and '1998-01-01'; 56 57explain (costs off) 58select count(*) from date_tbl 59 where f1 between symmetric '1997-01-01' and '1998-01-01'; 60select count(*) from date_tbl 61 where f1 between symmetric '1997-01-01' and '1998-01-01'; 62 63explain (costs off) 64select count(*) from date_tbl 65 where f1 not between symmetric '1997-01-01' and '1998-01-01'; 66select count(*) from date_tbl 67 where f1 not between symmetric '1997-01-01' and '1998-01-01'; 68 69 70-- 71-- Test parsing of a no-op cast to a type with unspecified typmod 72-- 73begin; 74 75create table numeric_tbl (f1 numeric(18,3), f2 numeric); 76 77create view numeric_view as 78 select 79 f1, f1::numeric(16,4) as f1164, f1::numeric as f1n, 80 f2, f2::numeric(16,4) as f2164, f2::numeric as f2n 81 from numeric_tbl; 82 83\d+ numeric_view 84 85explain (verbose, costs off) select * from numeric_view; 86 87rollback; 88 89 90-- 91-- Tests for ScalarArrayOpExpr with a hashfn 92-- 93 94-- create a stable function so that the tests below are not 95-- evaluated using the planner's constant folding. 96begin; 97 98create function return_int_input(int) returns int as $$ 99begin 100 return $1; 101end; 102$$ language plpgsql stable; 103 104create function return_text_input(text) returns text as $$ 105begin 106 return $1; 107end; 108$$ language plpgsql stable; 109 110select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); 111select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, null); 112select return_int_input(1) in (null, null, null, null, null, null, null, null, null, null, null); 113select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1, null); 114select return_int_input(null::int) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); 115select return_int_input(null::int) in (10, 9, 2, 8, 3, 7, 4, 6, 5, null); 116select return_text_input('a') in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'); 117 118rollback; 119 120-- Test with non-strict equality function. 121-- We need to create our own type for this. 122 123begin; 124 125create type myint; 126create function myintin(cstring) returns myint strict immutable language 127 internal as 'int4in'; 128create function myintout(myint) returns cstring strict immutable language 129 internal as 'int4out'; 130create function myinthash(myint) returns integer strict immutable language 131 internal as 'hashint4'; 132 133create type myint (input = myintin, output = myintout, like = int4); 134 135create cast (int4 as myint) without function; 136create cast (myint as int4) without function; 137 138create function myinteq(myint, myint) returns bool as $$ 139begin 140 if $1 is null and $2 is null then 141 return true; 142 else 143 return $1::int = $2::int; 144 end if; 145end; 146$$ language plpgsql immutable; 147 148create operator = ( 149 leftarg = myint, 150 rightarg = myint, 151 commutator = =, 152 negator = <>, 153 procedure = myinteq, 154 restrict = eqsel, 155 join = eqjoinsel, 156 merges 157); 158 159create operator class myint_ops 160default for type myint using hash as 161 operator 1 = (myint, myint), 162 function 1 myinthash(myint); 163 164create table inttest (a myint); 165insert into inttest values(1::myint),(null); 166 167-- try an array with enough elements to cause hashing 168select * from inttest where a in (1::myint,2::myint,3::myint,4::myint,5::myint,6::myint,7::myint,8::myint,9::myint, null); 169-- ensure the result matched with the non-hashed version. We simply remove 170-- some array elements so that we don't reach the hashing threshold. 171select * from inttest where a in (1::myint,2::myint,3::myint,4::myint,5::myint, null); 172 173rollback; 174