Repository navigation
Expand file tree
/
Copy pathpostgres.sql
More file actions
75 lines (65 loc) · 3.79 KB
/
Copy pathpostgres.sql
File metadata and controls
75 lines (65 loc) · 3.79 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- Nigeria's NDAPS postcode for PostgreSQL (tested on 14 and 17): two domains and a few functions.
-- Version 1.0.0
-- Plain SQL, so it loads on managed services that accept no extensions:
--
-- psql -f sql/postgres.sql
--
-- Store the compact form in an ng_postcode column. Behaviour follows spec/vectors.json,
-- as the libraries do; scripts/sql_conformance.py runs those cases against this file.
-- The version of this file, so that an installed copy can be told from a newer one.
CREATE FUNCTION ng_postcode_version() RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT '1.0.0' $$;
-- A well-formed code in its compact form, EK01A03FK01. Well formed is not assigned:
-- only the NIPOST API knows whether a code belongs to a building.
CREATE DOMAIN ng_postcode AS text COLLATE "C"
CHECK (VALUE ~ '^[A-Z]{2}(0[1-9]|[1-9][0-9])[A-Z0-9]{3}[A-Z]{2}(0[1-9]|[1-9][0-9])$');
-- A code cut off after one of its segments, such as the district EK01A03. A whole code
-- is the narrowest prefix.
CREATE DOMAIN ng_postcode_prefix AS text COLLATE "C"
CHECK (VALUE ~ '^[A-Z]{2}((0[1-9]|[1-9][0-9])([A-Z0-9]{3}([A-Z]{2}(0[1-9]|[1-9][0-9])?)?)?)?$');
-- The letters and digits of a hyphenated, spaced or compact code, in upper case, or NULL
-- if it holds anything else. translate() is used because upper() follows the locale.
CREATE FUNCTION ng_postcode_compact(text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT CASE WHEN $1 COLLATE "C" ~ '^[A-Za-z0-9 -]*$' THEN translate(
$1, 'abcdefghijklmnopqrstuvwxyz -', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') END
$$;
-- The code in any style as an ng_postcode, or NULL if it is not well formed.
CREATE FUNCTION ng_postcode_parse(text) RETURNS ng_postcode
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT CASE WHEN c ~ '^[A-Z]{2}(0[1-9]|[1-9][0-9])[A-Z0-9]{3}[A-Z]{2}(0[1-9]|[1-9][0-9])$'
THEN c::ng_postcode END
FROM (SELECT ng_postcode_compact($1) COLLATE "C") AS scanned(c)
$$;
-- The prefix in any style as an ng_postcode_prefix, or NULL if it is not well formed.
CREATE FUNCTION ng_postcode_prefix_parse(text) RETURNS ng_postcode_prefix
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT CASE WHEN c ~ '^[A-Z]{2}((0[1-9]|[1-9][0-9])([A-Z0-9]{3}([A-Z]{2}(0[1-9]|[1-9][0-9])?)?)?)?$'
THEN c::ng_postcode_prefix END
FROM (SELECT ng_postcode_compact($1) COLLATE "C") AS scanned(c)
$$;
-- The hyphenated form of a code or a prefix: EK-01-A03-FK-01, or EK-01-A03.
CREATE FUNCTION ng_postcode_format(ng_postcode_prefix) RETURNS text
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT concat_ws('-', substr($1, 1, 2), nullif(substr($1, 3, 2), ''),
nullif(substr($1, 5, 3), ''), nullif(substr($1, 8, 2), ''), nullif(substr($1, 10, 2), ''))
$$;
-- The code cut off after a segment: 'state', 'lga', 'district', 'area' or 'unit'.
-- NULL for any other name.
CREATE FUNCTION ng_postcode_truncate(ng_postcode, through text) RETURNS ng_postcode_prefix
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT left($1, CASE through WHEN 'state' THEN 2 WHEN 'lga' THEN 4 WHEN 'district' THEN 7
WHEN 'area' THEN 9 WHEN 'unit' THEN 11 END)::ng_postcode_prefix
$$;
-- The prefix one segment shorter, or NULL for a state.
CREATE FUNCTION ng_postcode_parent(ng_postcode_prefix) RETURNS ng_postcode_prefix
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT left($1, CASE length($1) WHEN 11 THEN 9 WHEN 9 THEN 7 WHEN 7 THEN 4 WHEN 4 THEN 2 END)
::ng_postcode_prefix
$$;
-- Whether a code starts with a prefix. In a WHERE clause, a range on the column uses its
-- index: code >= prefix AND code < prefix || '~'.
CREATE FUNCTION ng_postcode_within(ng_postcode, ng_postcode_prefix) RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
SELECT left($1, length($2)) = $2
$$;