q8DTQsxC

1drop schema if exists tac cascade;
2create schema tac;
3
4create domain tac.usascii_small_letters
5  as text
6  check ( value ~ '^[a-z]+$' );
7
8create domain tac.ucid as integer
9  check ( value between x'0'::integer and x'10ffff'::integer );
10
11create function tac.ucid_rng_diff( cid_1 tac.ucid, date_2 tac.ucid )
12  returns double precision
13  language sql
14  immutable
15  as $$
16    select cast( cid_1 - date_2 as double precision );
17  $$;
18
19-- A ----------------------------------------------------------------
20create type tac.ucid_rng as range (
21  subtype       = tac.ucid,
22  subtype_diff  = tac.ucid_rng_diff
23  );
24-- ----------------------------------------------------------------
25
26-- -- B ----------------------------------------------------------------
27-- set role dba; -- !!!!!!!!!!!!!!!!!!!!
28-- create type tac.ucid_rng;
29
30-- create function tac.ucid_rng_canonical( x tac.ucid_rng )
31--   returns tac.ucid_rng
32--   language plpgsql
33--   as $$
34--     begin
35--         if not lower_inc(x) then
36--             x := tac.ucid_rng(lower(x) + 1, upper(x), '[]');
37--         end if;
38--         if not upper_inc(x) then
39--             x := tac.ucid_rng(lower(x), upper(x) - 1, '[]');
40--         end if;
41--         return x;
42--     end;
43--   $$;
44
45-- create type tac.ucid_rng as range (
46--   subtype       = tac.ucid
47--   , subtype_diff  = tac.ucid_rng_diff
48--   , canonical     = tac.ucid_rng_canonical
49--   );
50
51-- reset role; -- !!!!!!!!!!!!!!!!!!!!
52-- -- ----------------------------------------------------------------
53
54create table tac.words (
55  word                    tac.usascii_small_letters,
56  cid                     tac.ucid,
57  cid_range               tac.ucid_rng
58  );
59
60insert into tac.words values
61  ( 'foo', 1, '[11,21]' ),
62  ( 'bar', 2, '[12,22]' ),
63  ( 'zip', 3, '[13,23]' ),
64  ( 'dat', 4, '[14,24]' ),
65  ( 'baz', 5, '[15,25]' );
66
67select * from tac.words where word between 'a' and 'c';
68select * from tac.words where word between 'a'::tac.usascii_small_letters and 'c';
69select * from tac.words where cid between 3 and 5;
70select * from tac.words where cid_range @> '[17,23]';
71select * from tac.words where cid_range @> '[17,23]'::tac.ucid_rng;
72select * from tac.words where cid_range @> 23::tac.ucid;
73select * from tac.words where cid_range @> 23;
74	
75ERROR:  operator does not exist: integer <@ tac.ucid_rng
76LINE 1: select * from tac.words where cid_range @> 23;
77HINT:  No operator matches the given name and argument type(s).
78You might need to add explicit type casts.
79	
80NOTICE:  argument type tac.ucid_rng is only a shell
81NOTICE:  return type tac.ucid_rng is only a shell
82ERROR:  PL/pgSQL functions cannot return type tac.ucid_rng
83	
84SELECT '[1,5]'::int4range @> 3;
85
86CREATE DOMAIN zdomain AS int;
87CREATE TYPE myrange_int     AS RANGE ( subtype = int );
88CREATE TYPE myrange_zdomain AS RANGE ( subtype = zdomain );
89
90-- WORKS
91SELECT '[1,5]'::myrange_int     @> 3;
92SELECT '[1,5]'::myrange_zdomain @> 3::zdomain;
93
94-- DOES NOT WORK
95SELECT '[1,5]'::myrange_zdomain @> 3;
96ERROR:  operator does not exist: myrange_zdomain @> integer
97LINE 1: SELECT '[1,5]'::myrange_zdomain @> 3;
98                                          ^
99HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.