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