Рецепты PostgreSQL: виджет Государственного Адресного Реестра

Для приготовления виджета Государственного Адресного Реестра сначала нужно его (ГАР) загрузить. При инициализации базы были созданы не только таблицы для загрузки в них ГАР, но также и таблица и функции для виджета. В этой статье остановимся на них подробнее.

Итак, для виджета будем использовать следующую иерархическую таблицу (и индексы), в которую поместим все актуальные данные по всем регионам:

CREATE TABLE IF NOT EXISTS gar ( -- создаём таблицу если её ещё нет     -- первичный ключ     id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,     parent uuid, -- родитель     name text NOT NULL, -- наименование     short text NOT NULL, -- краткий тип     type text NOT NULL, -- полный тип     post text, -- почтовый индекс     region smallint NOT NULL -- код региона ); CREATE INDEX IF NOT EXISTS gar_parent_idx ON gar USING btree (parent); CREATE INDEX IF NOT EXISTS gar_name_idx ON gar USING btree (name); CREATE INDEX IF NOT EXISTS gar_short_idx ON gar USING btree (short); CREATE INDEX IF NOT EXISTS gar_type_idx ON gar USING btree (type); CREATE INDEX IF NOT EXISTS gar_region_idx ON gar USING btree (region);

Далее определим вспомогательную функцию, которую будем использовать для получения полного наименования

-- создаём или меняем функцию от наименования, краткого и полного типа CREATE OR REPLACE FUNCTION gar_text(name text, short text, type text) RETURNS text -- возвращающую полное наименование LANGUAGE sql -- использующую язык sql IMMUTABLE AS $body$ -- зависящую только от своих параметров     select case -- в случае     -- когда полный тип не определён, возвратить наименование         when gar_text.type in ('Не определено') then gar_text.name         -- когда полный тип - Чувашия, возвратить наименование и полный тип         when gar_text.type in ('Чувашия') then gar_text.name||' '||gar_text.type         -- когда в наименовании содержится полный тип, возвратить только наименование         when gar_text.name ilike '%'||gar_text.type||'%' then gar_text.name         -- иначе возвратить краткий тип точка наименование         else gar_text.short||'.'||gar_text.name     end; $body$;

Также создадим представление с добавлением этой функции в качестве колонки

CREATE OR REPLACE VIEW gar_view AS -- создаём или меняем представление SELECT gar.*, -- выбирая всё gar_text(name, short, type) AS text -- добавляя полное наименование from gar; -- из ГАРа

Далее определим вспомогательную функцию для подсчёта количества дочерних элементов

-- создаём или меняем функцию от идентификатора CREATE OR REPLACE FUNCTION gar_child(id uuid) -- возвращающую количесвтво его дочерних элементов RETURNS bigint LANGUAGE sql STABLE AS $body$     select count(1) from gar where parent = gar_child.id; $body$;

Также, вспомогательную функцию для выбора, если задан массив идентификаторов

-- создаём или меняем функцию от массива идентификаторов CREATE OR REPLACE FUNCTION gar_select(id uuid[]) RETURNS SETOF gar_view -- возвращающую набор строк из представления LANGUAGE sql STABLE AS $body$ -- выбираем всё, в т.ч. и полное наименование     select gar.*, gar_text(name, short, type) AS text from gar     -- в указанном агрументом функции порядке     inner join (select unnest(gar_select.id) as id,                 generate_subscripts(gar_select.id, 1) as i) as _ on _.id = gar.id     -- для указанных в аргументе функции идентификаторов     where gar.id = any(gar_select.id) order by i; $body$;

И ещё вспомогательную функцию для рекурсивного выбора вверх родителей указанного идентификатора, ограничиваясь указанным родительским идентификатором

CREATE OR REPLACE FUNCTION gar_select( -- создаём или меняем функцию от id uuid, -- идентификатора parent uuid DEFAULT NULL -- и необязательного родительского идентификатора ) RETURNS SETOF gar_view -- возвращающую набор строк представления LANGUAGE sql STABLE AS $body$     with recursive _ as ( -- рекурсивно       -- сначала выбирая по указанному идентификатору         select gar.*, 0 as i from gar where id = gar_select.id         union       -- а потом выбирая родителей вверх до указанного родителя или до самого верха         select gar.*, _.i + 1 as i from gar inner join _ on (_.parent = gar.id)         where gar_select.parent is null or _.parent != gar_select.parent     ) select id, parent, name, short, type, post, region, gar_text(name, short, type) AS text      from _ order by i desc; $body$;

Также вспомогательную функцию для поиска

CREATE OR REPLACE FUNCTION gar_select( -- создаём или меняем функцию parent uuid, -- от родителя   name text, -- наименования   short text, -- краткого типа   type text, -- полного типа   post text, -- почтового индекса   region text -- кода региона ) RETURNS SETOF gar_view -- возвращающую набор строк представления LANGUAGE sql STABLE AS $body$ -- выбираем всё, где     select gar.*, gar_text(gar.name, gar.short, gar.type) AS text from gar where true     -- если не задан родитель то без родителя, а если задан - то по нему     and ((gar_select.parent is null and parent is null) or parent = gar_select.parent) -- если задано наимнование, то ищем по нему сначала или после пробела или после дефиса или после точки         and (gar_select.name is null or name ilike gar_select.name||'%' or name ilike '% '||gar_select.name||'%' or name ilike '%-'||gar_select.name||'%' or name ilike '%.'||gar_select.name||'%')     -- если задан кратки тип, то ищем по нему     and (gar_select.short is null or short ilike gar_select.short)     -- если задан полный тип и это массив - то учитываем все элементы массива, а если не массив, то сначала     and (gar_select.type is null or case when gar_select.type ilike '{%}' then type = any(gar_select.type::text[]) else type ilike gar_select.type||'%' end)     -- если задан почтовый индекс, то ищем по нему сначала     and (gar_select.post is null or post ilike gar_select.post||'%')     -- если задан код региона и это массив - то учитываем все элементы массива, а если не массив - то ищем по нему      and (gar_select.region is null or case when gar_select.region ilike '{%}' then region = any(gar_select.region::smallint[]) else region = gar_select.region::smallint end)     -- сортируем сначала по численному наименованию, а потом по наименованию     order by to_number('0'||name, '999999999'), name; $body$;

И ещё вспомогательную функцию для поиска родителей

-- создаём или меняем функцию от родителя, наименования, ... CREATE OR REPLACE FUNCTION gar_select_parent(parent uuid, name text, short text, type text, post text, region text) RETURNS SETOF gar_view LANGUAGE sql STABLE AS $body$ -- выбираем всё     select gar.*, gar_text(gar.name, gar.short, gar.type) AS text from gar     -- где полный тип из указанного массива     where type = any(gar_select_parent.type::text[])     -- и если заданно наименование , то ищем по нему или после пробела или после дефиса или после точки     and (gar_select_parent.name is null or name ilike gar_select_parent.name||'%' or name ilike '% '||gar_select_parent.name||'%' or name ilike '%-'||gar_select_parent.name||'%' or name ilike '%.'||gar_select_parent.name||'%') -- сортируя по глубине вложенности         order by (select count(id) from gar_select(id, gar_select_parent.parent)), to_number('0'||name, '999999999'), name; $body$;

Ну и теперь, главную функцию для виджета

-- создаём или меняем функцию от json, возвращающую json CREATE OR REPLACE FUNCTION gar_select(INOUT json json) RETURNS json LANGUAGE plpgsql STABLE AS $body$ <<local>> declare     id text default nullif(trim(gar_select.json->>'id'), ''); -- уид     parent text default nullif(trim(gar_select.json->>'parent'), ''); -- уид родителя     name text default nullif(trim(gar_select.json->>'name'), ''); -- наименование     short text default nullif(trim(gar_select.json->>'short'), ''); -- кратко     type text default nullif(trim(gar_select.json->>'type'), ''); -- тип     post text default nullif(trim(gar_select.json->>'port'), ''); -- индекс     region text default nullif(trim(gar_select.json->>'region'), ''); -- регион     text text default nullif(trim(gar_select.json->>'text'), ''); -- строка поиска     offset int default coalesce(nullif(trim(gar_select.json->>'offset'), '')::int, 0); -- офсет     limit int default coalesce(nullif(trim(gar_select.json->>'limit'), '')::int, 10); -- лимит     "full" boolean default coalesce(nullif(trim(gar_select.json->>'full'), '')::boolean, false); -- все?     child boolean default coalesce(nullif(trim(gar_select.json->>'child'), '')::boolean, false); -- дети? begin     if local.id is not null then -- если задан id         local.id = translate(local.id, '[]','{}');         if local.id ilike '{%}' then -- если id - массив             if local.full then -- если все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.id::uuid[])                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select * from _ offset local.offset limit local.limit                         ) select coalesce(json_agg((select json_agg(_) from (                             select *, case when local.child then gar_child(_.id) end as child from gar_select(_.id::uuid)                         ) as _)), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             else -- иначе - не все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.id::uuid[])                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select *, case when local.child then gar_child(_.id) end as child from _ offset local.offset limit local.limit                         ) select coalesce(json_agg(_), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             end if;         else  -- иначе id - не массив             if local.full then -- если все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.id::uuid)                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select * from _ offset local.offset limit local.limit                         ) select coalesce(json_agg((select json_agg(_) from (                             select *, case when local.child then gar_child(_.id) end as child from gar_select(_.id::uuid)                         ) as _)), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             else -- иначе - не все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.id::uuid)                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select *, case when local.child then gar_child(_.id) end as child from _ offset local.offset limit local.limit                         ) select coalesce(json_agg(_), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             end if;         end if;     else -- иначе - не задан id         if local.text is not null then -- если искать что-то             local.name = local.text;             local.short = split_part(local.name, '.', 1);             if local.short = local.name or position(' ' in local.short) > 0 or position(',' in local.short) > 0 then                 local.short = null;             else                 local.name = split_part(local.name, '.', 2);             end if;             local.name = ltrim(local.name, ' ');         end if;         if local.text is not null and local.parent is null then -- если искать что-то и родитель не задан             with _ as (                 with _ as (                     select * from gar_select_parent(local.parent::uuid, local.name, local.short, array['Город', 'Поселок', 'Поселение', 'Деревня', 'Населенный пункт', 'Село', 'Рабочий поселок', 'Поселок городского типа']::text, local.post, local.region)                 ) select count(1), gar_select.json as query, local.offset, local.limit, (                     with _ as (                         select * from _ offset local.offset limit local.limit                     ) select coalesce(json_agg((select json_agg(_) from (                         select * from gar_select(_.id, local.parent::uuid)                     ) as _)), '[]'::json) from _                 ) as data from _             ) select to_json(_) from _ into strict json;         else             local.type = translate(local.type, '[]','{}');             if local.full then -- если все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.parent::uuid, local.name, local.short, local.type, local.post, local.region)                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select * from _ offset local.offset limit local.limit                         ) select coalesce(json_agg((select json_agg(_) from (                             select *, case when local.child then gar_child(_.id) end as child from gar_select(_.id::uuid)                         ) as _)), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             else -- иначе - не все результаты                 with _ as (                     with _ as (                         select * from gar_select(local.parent::uuid, local.name, local.short, local.type, local.post, local.region)                     ) select count(1), gar_select.json as query, local.offset, local.limit, (                         with _ as (                             select *, case when local.child then gar_child(_.id) end as child from _ offset local.offset limit local.limit                         ) select coalesce(json_agg(_), '[]'::json) from _                     ) as data from _                 ) select to_json(_) from _ into strict gar_select.json;             end if;         end if;     end if; end;$body$;