a65495f264
Fixes `housenumber_display.sql` function and further improves https://github.com/openmaptiles/openmaptiles/pull/1632 Parsing text values that contain `;`, the string was split into array of values which were casted to `integer`. However, that fails for housenumber that exceed Postgres `int` range `-2147483648` to `+2147483647`. There are such housenumber, e.g.: https://www.openstreetmap.org/way/1091331906#map=19/-3.82721/-38.59269
21 lines
858 B
PL/PgSQL
21 lines
858 B
PL/PgSQL
CREATE OR REPLACE FUNCTION display_housenumber_nonnumeric(raw_housenumber text)
|
||
RETURNS text AS $$
|
||
-- Find the position of the semicolon in the input string
|
||
-- and extract the first and last value
|
||
SELECT substring(raw_housenumber from 1 for position(';' in raw_housenumber) - 1)
|
||
|| '–'
|
||
|| substring(raw_housenumber from position(';' in raw_housenumber) + 1);
|
||
$$ LANGUAGE SQL IMMUTABLE;
|
||
|
||
|
||
CREATE OR REPLACE FUNCTION display_housenumber(raw_housenumber text)
|
||
RETURNS text AS $$
|
||
SELECT CASE
|
||
WHEN raw_housenumber !~ ';' THEN raw_housenumber
|
||
WHEN raw_housenumber ~ '[^0-9;]' THEN display_housenumber_nonnumeric(raw_housenumber)
|
||
ELSE
|
||
(SELECT min(value)::text || '–' || max(value)::text
|
||
FROM unnest(array_remove(string_to_array(raw_housenumber, ';'), '')::bigint[]) AS value)
|
||
END
|
||
$$ LANGUAGE SQL IMMUTABLE;
|