-- basic bounds: country only
SELECT gsscode_range_lower('E')::text;
 gsscode_range_lower 
---------------------
 E00000000
(1 row)

SELECT gsscode_range_upper('E')::text;   -- first value of the next country, F
 gsscode_range_upper 
---------------------
 F00000000
(1 row)

-- basic bounds: country+type
SELECT gsscode_range_lower('E01')::text;
 gsscode_range_lower 
---------------------
 E01000000
(1 row)

SELECT gsscode_range_upper('E01')::text; -- first value of the next type, E02
 gsscode_range_upper 
---------------------
 E02000000
(1 row)

-- full 9-character code: range of exactly one value
SELECT gsscode_range_lower('E01000001')::text;
 gsscode_range_lower 
---------------------
 E01000001
(1 row)

SELECT gsscode_range_upper('E01000001')::text;
 gsscode_range_upper 
---------------------
 E01000002
(1 row)

SELECT gsscode_range_lower('E01000001') = 'E01000001'::gsscode;
 ?column? 
----------
 t
(1 row)

SELECT gsscode_range_upper('E01000001') = 'E01000002'::gsscode;
 ?column? 
----------
 t
(1 row)

-- invalid-length prefixes raise, unlike %/!% which just never match
SELECT gsscode_range_lower('E0100');
ERROR:  invalid gsscode prefix
HINT:  expected a country letter, a country+type (eg "E01"), or a full 9-character code
SELECT gsscode_range_lower('E0');
ERROR:  invalid gsscode prefix
HINT:  expected a country letter, a country+type (eg "E01"), or a full 9-character code
SELECT gsscode_range_lower('');
ERROR:  invalid gsscode prefix
HINT:  expected a country letter, a country+type (eg "E01"), or a full 9-character code
-- the core soundness property: for every prefix length and every real
-- row, the range form and the %% boolean form must agree exactly
CREATE TEMP TABLE range_sample (code gsscode);
INSERT INTO range_sample
   SELECT ('E01' || lpad(i::text, 6, '0'))::gsscode FROM generate_series(1,50) i;
INSERT INTO range_sample
   SELECT ('E02' || lpad(i::text, 6, '0'))::gsscode FROM generate_series(1,20) i;
INSERT INTO range_sample VALUES ('W01000001'), ('S01000001'), ('K02000001');
SELECT count(*) AS disagreements FROM range_sample
WHERE (code % 'E01')
  IS DISTINCT FROM (code >= gsscode_range_lower('E01') AND code < gsscode_range_upper('E01'));
 disagreements 
---------------
             0
(1 row)

SELECT count(*) AS disagreements FROM range_sample
WHERE (code % 'E')
  IS DISTINCT FROM (code >= gsscode_range_lower('E') AND code < gsscode_range_upper('E'));
 disagreements 
---------------
             0
(1 row)

-- range form actually returns the right rows
SELECT count(*) FROM range_sample
WHERE code >= gsscode_range_lower('E01') AND code < gsscode_range_upper('E01');
 count 
-------
    50
(1 row)

CREATE INDEX ON range_sample USING btree (code);
ANALYZE range_sample;
-- range form drives a genuine index scan (ordinary </>= strategies,
-- no opfamily trickery) -- confirmed live via EXPLAIN separately, not
-- asserted here since plan cost/text isn't stable output for pg_regress
SELECT count(*) FROM range_sample
WHERE code >= gsscode_range_lower('E01') AND code < gsscode_range_upper('E01');
 count 
-------
    50
(1 row)