Рецепты 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$;

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

CREATE OR REPLACE FUNCTION gar_text( -- создаём или меняем функцию
  id uuid, -- от идентификатора
  post boolean DEFAULT NULL, -- добавлять почтовый индекс?
  "full" boolean DEFAULT NULL -- добавлять подъезд и этаж? (пока не используется)
) RETURNS text LANGUAGE sql STABLE AS $body$ -- возвращающую текст
    with _ as (
        with _ as (
            select * from gar_select(gar_text.id) -- выбираем всё
        ), p as (
          	-- если добавлять индекс, то выбираем самый дочерний
            select unnest(array_agg(post)) as post, generate_subscripts(array_agg(post), 1) as i from _ where coalesce(true, false) and post is not null order by i desc limit 1
        ) select post as text from p union select string_agg(gar_text(name, short, type), ', ') as text from _ where type not in ('Подъезд', 'Этаж') or coalesce(gar_text.full, false)
    ) select string_agg(text, ', ') from _
$body$;

и алиас для её быстрого вызова

CREATE OR REPLACE FUNCTION gar_full(id uuid) RETURNS text LANGUAGE sql STABLE AS $body$
    select gar_text(gar_full.id, true, true);
$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$;