Преобразование номеров домов КЛАДР в множество

В КЛАДР номера домов представлены пятью способами.

1. Непосредственно номер дома (1А)
2. Последовательность номеров домов (1А,1В)
3. Диапазоном номеров домов (1-10)
4. Последовательность домов с четными номера (Ч(1-10))
5. Последовательность домов с нечетными номерами (Н(1-10))

Преобразуем 1 и 2 представление в множество.

SELECT

CODE,

TYPE,

HOUSE

FROM

(SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T1

WHERE

HOUSE!~'^[0-9]+-[0-9]+$' AND

HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';

Преобразуем 3 представление в множество.

WITH RECURSIVE T1 as (

SELECT row_number() over() as RN, string_to_array(HOUSE, '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE AS TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^[0-9]+-[0-9]+$'),

T2(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T1 UNION ALL SELECT T2.RN, T2.CODE, T2.TYPE, T2.HOUSE + 1 AS HOUSE FROM T1 INNER JOIN T2 ON T2.RN=T1.RN WHERE T2.HOUSE >= T1.HOUSE[1]::INT AND (T2.HOUSE + 1) <= T1.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

HOUSE

FROM

T2;

Преобразуем 4 и 5 представление в множество.

WITH RECURSIVE T3 as (

SELECT row_number() over() as RN, string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),

T4(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T3 UNION ALL SELECT T4.RN, T4.CODE, T4.TYPE, T4.HOUSE + 2 AS HOUSE FROM T3 INNER JOIN T4 ON T4.RN=T3.RN WHERE T4.HOUSE >= T3.HOUSE[1]::INT AND (T4.HOUSE + 2) <= T3.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

HOUSE

FROM

T4;

Объединим получившиеся решения.

WITH RECURSIVE T1 as (

SELECT row_number() over() as RN, string_to_array(HOUSE, '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE AS TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^[0-9]+-[0-9]+$'),

T2(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T1 UNION ALL SELECT T2.RN, T2.CODE, T2.TYPE, T2.HOUSE + 1 AS HOUSE FROM T1 INNER JOIN T2 ON T2.RN=T1.RN WHERE T2.HOUSE >= T1.HOUSE[1]::INT AND (T2.HOUSE + 1) <= T1.HOUSE[2]::INT),

T3 as (

SELECT row_number() over() as RN, string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),

T4(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T3 UNION ALL SELECT T4.RN, T4.CODE, T4.TYPE, T4.HOUSE + 2 AS HOUSE FROM T3 INNER JOIN T4 ON T4.RN=T3.RN WHERE T4.HOUSE >= T3.HOUSE[1]::INT AND (T4.HOUSE + 2) <= T3.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

to_char(HOUSE, '99999')

FROM

T2

UNION ALL
SELECT

CODE,

TYPE,

to_char(HOUSE, '99999')

FROM

T4

UNION ALL
SELECT

CODE,

TYPE,

HOUSE

FROM

(SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T1

WHERE

HOUSE!~'^[0-9]+-[0-9]+$' AND

HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';