Адреса ФИАС в среде PostgreSQL

Views/Просмотры: 3 171

Как бы мы не относились к качеству адресов ФИАС с ними необходимо работать, потому что это единственный общероссийский справочник адресов. Поэтому рано или поздно приходится решать задачу связи местоположения объектов недвижимости, юридических и физические адресов с адресом из ФИАС.

В этой статье изложен опыт работы со списком адресообразующих элементов ФИАС, загруженных в базу данных под управлением PostgreSQL. Для работы с адресообразующими элементами ФИАС создано несколько функций на языке PL/pgSQL.

Места постоянного размещения статьи:

В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций, а также скрипты для создания таблицы с записями адресообразующих элементов ФИАС, а также загрузки данных в это таблицу из файла в формате CSV. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению
или скачать все скрипты одним архивом.

Родословная адресообразующего элемента
Начнем с примера.
Вызов функции fstf_AddressObjects_AddressObjectTree(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’) в операторе SELECT приведет к получению следующего списка записей.

Таблица 1. Результат выполнения функции fstf_AddressObjects_AddressObjectTree(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’).

При внимательном рассмотрении можно заметить, что в качестве аргумента функции передан идентификатор элемента (AOGUID) «улица Новая», в результате получены четыре записи:

запись с характеристиками самой улицы Новая;
три родительских записи о поселке, районе и крае, которым принадлежит улица.

У функции есть еще одни необязательный параметр признак актуальности (CurrStatus), с помощью которого можно просматривать родословную не только актуального адресообразующего элемента, но и уже устаревшего.
Полный текст функции приведен в Приложении в подразделе Создание функции fstf_AddressObjects_AddressObjectTree.

С самого начала

Если вы знаете как устроены таблицы ФИАС, то этот раздел можно пропустить.

Потребность в такой функции продиктована тем, что список адресообразующих элементов ФИАС (ADDROBJ) представляет собой древовидную структуру, которой каждый элемент ссылается на идентификатор (AOGUID) родительской записи значением поля ParentGUID. Т.е. просматривая записи исходного списка, обычно наблюдаешь длинный список улиц. Для того чтобы определить в каком населенном пункте находится улица нужно по значению ParentGUID найти записи с таким идентификатором элемента.

Рис. 1. Иерархия адресообразующих элементов ФИАС.

Это не описка и не оговорка. По значению ParentGUID может быть найдена не одна, а несколько записей. Из чего следует, что идентификатор адресообразующего элемента не является первичным ключом для таблицы, содержащей список ADDROBJ.
Дело в том, что список адресообразующих элементов, вместе с каждым элементом хранит историю его «переименования». Т.е. под одним идентификатором элемента хранится не только текущее название элемента, но его прежние названия. Т.е., отдельная запись списка ADDROBJ хранит данные об адресообразующем элементе, а также характеристики календарного периода, в течении которого было актуальным название элемента.

Таблица 2. История улицы «Красноярский край, р-н Таймырский Долгано-Ненецкий, г Дудинка, п Левинские Пески, ул Береговая»

Порядок следования периодов актуальности названия адресообразующего элемента, может быть определен путем просмотра двух разнонаправленных списков. Для этого каждая запись о периоде актуальности элемента содержит два указателя на предыдущий (PrevID) и последующий периоды (NextID). У первого периода адресообразующего элемента отсутствует указатель на предыдущий период, а у последнего (актуального) отсутствует указатель на следующий период.

Рис. 2. Основные поля записи адресообразующего элемента ФИАС.

Период актуальности характеризуется датами начала и окончания периода, соответственно StartDate и EndDate. При этом даты начала первого периода и окончания последнего имеют условные значения. За дату начала первого периода принимается «01.01.1900 0:00», а за дату окончания последнего (актуального) периода принимается «06.06.2079 0:00».
Актуальное (ныне действующее) название адресообразующего элемента указано в записи о последнем периоде, при условии что он не завершен, т.е. дата окончания периода больше или равна текущей дате.
Для упрощения поиска записи об актуальном периоде элемента, кроме даты начала и окончания периода, введены еще два поля: CurrStatus и ActStatus.
ActStatus принимает вполне ожидаемые значения: «1» – актуальная версия характеристик элемента, «0» – не актуальная или историческая версия, как обозначено в справочнике.
Со значениями поля CurrStatus дело обстоит сложнее. При помощи его значений решаются две задачи одновременно: устанавливается идентификатор каждой версии записи об адресообразующем элементе и присваивается признак актуальности записи. Поэтому последняя актуальная запись об элементе содержит значение 0 в этом поле, а все исторические записи нумеруются в порядке появления – «1» самая ранняя запись, следующая за ней по времени – «2» и т.д. Таблица 2 содержит список записей с версиями описания улицы Береговой посёлка Левинские Пески. В этом списке предыдущая к актуальной записи содержит «7» в поле CurrStatus.

Как это работает

Рис. 3. Упрощенная реализация функции fstf_AddressObjects_AddressObjectTree

исходный код оператора на рисунке

Для реализации функции напрашивается использовать рекурсивный запрос подобный тому, который приведен на Рис. 3, где a_AOGUID идентификатор адресообразующего элемента, a_CurrStatus признак актуальности по КЛАДР. Оба значения передаются функции через параметры.
Как всякий рекурсивный запрос, этот состоит из двух частей: в первой части находится первая запись элемента с идентификатором a_AOGUID, в следующей рекурсивной части последовательно находятся все актуальные родительские записи по отношению к записям, полученным процессе предыдущих итераций. Переход к родительскому элементу выполняется по ссылке ParentGUID текущей записи.
Здесь важно, чтобы каждая итерация приводила к получению только одной записи. Для этого накладывается ограничение на значение признака CurrStatus. Пример результата применения рекурсивного запроса в условиях отсутствия уникальности записи хотя бы на одном шаге итерации приведен в Рис. 3.
Возникает вопрос – почему ограничение на значение CurrStatus реализовано через вложенный подзапрос, а не путем присвоения актуальных значений? Во-первых, потому, что в актуальной записи об элементе не всегда CurrStatus=0, как это демонстрирует Таблица 4. Во-вторых, необходимо, чтобы функция возвращала результат для неактуальных названий элемента. В том числе, даже в том случае, когда на пути между начальным и конечным элементами встретится элемент, для которого не объявлено ни одной актуальной записи.

Таблица 3. Результат выполнения функции над элементом с не уникальным признаком выбора записи

Если a_CurrStatus =0 использовать как условие выбора единственной записи для элемента, то функция не вернет для записей о городе Заозерный, записи о котором приведены в Таблица 4. При этом, если задать a_CurrStatus =51, то результат будет таким, как показано в Таблица 3.

ao.currstatus = (SELECT MIN(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)

1	ao.currstatus = (SELECT MIN(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)

Это условие, которое присутствует во второй части рекурсивного запроса, не совсем корректно, если исходить из правила присвоения значений для признака CurrStatus. Действительно, если среди записей адресообразующего элемента нет актуального (CurrStatus=0), то наиболее «свежей» является запись с максимальным, т.е. последним использованным, значением признака CurrStatus. Когда используется выше приведенное условие, то из записей неактуального элемента выбирается самая старая.

Таблица 4. Элемент с повторяющимся не равным нулю значением CurrStatus

Поэтому, более правильным решением будет использование следующего условия:

ao.currstatus = CASE WHEN 0 &lt; ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
      ELSE 0 END</pre>

ao.currstatus = CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)

THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)

ELSE 0 END</pre>

Но, в этом условии два подзапроса. Так что приходится выбирать между смысловой строгостью запроса и эффективностью его выполнения.
Оправданием использования первого варианта условия служит фактическое отсутствие различий названий адресообразующего элемента в исторических записях. Так по состоянию на 13 октября 2016 года, из 26728 адресообразующих элементов Красноярского края в 19865 присутствуют исторические записи. При этом лишь для 1350 элементов (6,8% от числа элементов, имеющих историю) присутствуют различия в названиях одного и того же элемента. Т.е. для 93,2% элементов первое и второе условия будут возвращать один и тот же список названий. Отличия возможны только в значениях признака CurrStatus, которыми, учитывая назначение функции, можно пренебречь.
Полностью замена признака CurrStatus на ActStatus невозможна. По условию ActStatus=1 однозначно выбирается актуальная запись об элементе, но для работы с историческими записями приходится использовать оба признака. Такое решение приведено в приложении Создание функции fstf_AddressObjects_AddressObjectTree.

Полное наименование адресообразующего элемента

Основная идея функции fsfn_AddressObjects_TreeActualName в том, чтобы возвратить соединенные в одну строку название элемента вместе с названиями всех его предков.
Например, пусть функции поиска родословной элемента (fstf_AddressObjects_AddressObjectTree) возвращает следующий список значений.

Таблица 5. Результат вызова fstf_AddressObjects_AddressObjectTree .

Тогда fsfn_AddressObjects_TreeActualName(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’) должна возвратить:
«Красноярский край, Балахтинский р-н, п Могучий, ул Новая»

У функции есть еще одни необязательный параметр массив масок (a_MaskArray),с помощью которого можно включать в результат не все названия элементов, а только те, которые нужны.

Текст функции приведен в разделе Приложения «Создание функции fsfn_AddressObjects_TreeActualName».

Как это работает

Основу реализации функции составляет вызов fstf_AddressObjects_AddressObjectTree и цикл по возвращаемым ей записям, в теле которой формируется поллное наименование адресообразующего элемента путем сцепления (конкатенации) всех наименований в одну строку. Эта строка в конце концов будет возвращения функцией fsfn_AddressObjects_TreeActualName.
Далее будут поясняться детали.
Во-первых, порой нет необходимости в том, чтобы результат функции обязательно включал наименования всех предков текущего элемента. Например, в пределах Красноярского края вместо «Красноярский край, Балахтинский р-н, п Могучий, ул Новая», чаще используют укороченную форму «Балахтинский р-н, п Могучий, ул Новая». А внутри города Красноярска вместо адреса «Красноярский край, г Красноярск, д Песчанка, ул Сергея Лазо» чаще используют «д Песчанка, ул Сергея Лазо».
Для того чтобы иметь возможность управлять разными формами написания полного наименования адресообразующего элемента введен параметр массив масок (a_MaskArray), который содержит последовательность указателей (масок) на группы элементов, которые должны быть включены в результирующую строку.

Таблица 6. Список масок функции.

Во-вторых, для того чтобы реализовать построение полного наименования в соответствии с массивом масок создана вспомогательная функция fsfn_AddressObjects_ObjectGroup, которая относит каждый адресообразующий элемент к определенной группе.

Таблица 7. Значения, возвращаемые функцией fsfn_AddressObjects_ObjectGroup.

Список значений, возвращаемых функцией fsfn_AddressObjects_ObjectGroup, приведен в Таблица 5.
Цель создания этой функции в том чтобы собрать в одном месте все особенности (если хотите, то «костыли») определения группы элемента. С детальной реализацией этой функции можно ознакомиться в разделе «Создание функции fsfn_AddressObjects_ObjectGroup».
Комбинация значений функции и поля AOLevel (уровень адресообразующего элемента) вместе с проверкой на присутствие маски группы массиве масок позволяет определять должно ли название текущего элемента включаться в строку результата.
Например, признаком того, что название основного населенного пункта должно включаться в полное наименование элемента, является истинность следующего выражения: «v_ObjectGroup=’City’ AND ‘{LM}’ <@ a_MaskArray AND v_AOLevel =4».

Поиск адресообразующего элемента

Функция fstf_AddressObjects_SearchByName предназначена для поиска адрсообразующих элементов ФИАС по их названиям. При этом поиск может осуществялться не только по названию и типу текущего элемента, но и названиям и типам одного или двух его ближайших предков.
Рассмотрим несколько примеров с примера.
Для начала найдем все адресообразующие элементы в названии которых встречается слово «Грибной».

Таблица 8. Результат выполнения функции fstf_AddressObjects_SearchByName(‘Грибной’).

В полученном результате нет ничего неожиданного, если не считать наглядного доказательства пользы от функции построения полного наименования.
Теперь изменим запрос. Найдем все адресообразующие элементы, в названии ближайшего предка которых встречается слово «Грибной».

Таблица 9. Результат выполнения функции fstf_AddressObjects_SearchByName(NULL,NULL,’Грибной’)

5ae71e68-5477-446b-b878-0a9c9bf3bdcd 7 Ачинский р-н, п Грибной, ул Южная ул Южная 0 1 п Грибной
Результат этого запроса несколько более неожиданный, т.к. в названиях найденных адресообразующих элементах нет слова «Грибной», но оно есть в названии их предка.
И, наконец, рассмотрим поиск по наименованию прародителя, в котором должно присутствовать слово «Ачинский», а в наименовании его внука должен присутствовать часть слова «Оз_рн». Здесь использован специальный символ — символ подчеркивания «_». Этот символ указывает, что на его месте может находиться любой одиночный символ. Здесь он применен, для того чтобы найти не только элементы с названиями «Озерный» или «Озерная», но и «Озёрный» или «Озёрная».

Таблица 10. Результат выполнения функции
fstf_AddressObjects_SearchByName(‘Оз_рн’,NULL,NULL,NULL,’Ачинский’)

В результате запроса найдены улицы Озерная и переулок озерный в трех населенных пунктах Ачинского и Казачинского районов Красноярского края.
Текст функции приведен в разделе Приложения «Создание функции fstf_AddressObjects_SearchByName».

Как это работает

В реализации fstf_AddressObjects_SearchByName нет ничего необычного, если не считать того, что одно из полей возвращается функцией построения полного наименования адресообразующего элемента — fsfn_AddressObjects_TreeActualName.
У функции 6 аргументов: a_FormalName, a_ShortName, a_ParentFormalName, a_ParentShortName, a_GrandParentFormalName, a_GrandParentShortName. Задавать значение можно только первому аргументу, остальные будут получать значения по умолчанию.
Если значения присвоены только первым двум аргументам – названию (a_FormalName) и типу (a_ShortName) адресообразующего элемента, то поиск осуществляется во всех записях таблицы fias_AddressObjects. Предварительно значения переданных параметров преобразуются в верхний регистр, пробелы заменяются символом «%». Этим же символом окружается значение справа и слева. Преобразованные таким образом значения используются в поисковом запросе как часть операции LIKE. Пример такого запроса приведен на Рис. 4.

Рис. 4. Простой поиск адресообразующего элемента.

исходный код оператора на рисунке

Условие выбора значения CurrStatus подробно обсуждалось в разделе «Родословная адресообразующего элемента» «Как это работает».

Рис. 5. Поиск по названию и типу родительского адресообразующего элемента.

Для поиска по названию и типу родительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: третьему (a_ParentFormalName), или четвертому (a_ParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского адресообразующего элемента по признаку pfa.AOGUID=cfa.ParentGUID. Пример такого запроса приведен на Рис. 5.
Предварительная обработка значений входных параметров осуществляется по тем же правилам, как и в случае простого поиска.

Для поиска по названию и типу прародительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: пятому (a_GrandParentFormalName) или шестому (a_GrandParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной двойным соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского и прародительских адресообразующих элементов. . Пример такого запроса приведен на Рис. 6.
Предварительная обработка значений входных параметров осуществляется по тем же правилам, как и в случае простого поиска.

Рис. 6. Поиск по названию и типу прародительского адресообразующего элемента.

Эпилог

Этот раздел содержит рекомендации к тому, как загрузить список адресообразующих элементов ФИАС в таблицу fias_AddressObjects.

С чего начать

Начать надо с посещения официального сайта Федеральной Налоговой Службы раздела «Федеральная информационная адресная система» (ФИАС) страницы «Обновления».
Загрузить на ваш компьютер последнее обновление или полную базу ФИАС, если вы только начинаете работать с ФИАС.
Перенести файл с архивом в рабочую папку. Извлечь файлы архива и найти файл ADDROBJ.DBF.
Далее предполагается, что загружен архив файлов с обновлением ФИАС в формате dbf.
Загруженный файл ADDROBJ.DBF преобразовать к формату csv. Для этого открыть исходный файл при помощи MS Excel и пересохранить его в формате в csv, не забыв при этом удалить строку с названиями полей записей. Далее преобразованный к формату csv будет именоваться «ADDROBJ24_20161020.csv», где 24 –код Красноярского края, а 20161020 – дата загрузки файла.
Создать таблицу fias_AddressObjects. Для этого можно воспользоваться скриптом приведенным в приложении «Создание таблицы адресообразующих элементов ФИАС fias_AddressObjects».

Загрузка ADDROBJ24_20161020.csv в базу данных

Рис. 7 Непосредственная загрузка данных в таблицу fias_AddressObjects.

Непосредственно загрузить данные из файла ADDROBJ24_20161020.csv в таблицу fias_AddressObjects можно так как показано на Рис. 7.
Но, к сожалению, простой путь не для нас.
Непосредственно загрузить данные из файла ADDROBJ24_20161020.csv в таблицу fias_AddressObjects можно так как показано на Рис. 7.
Но, к сожалению, простой путь не для нас.
Во-первых, кроме основного списка адресообразующих элементов поставляется еще и список адресообразующих элементов которые должны быть удалены из основного списка (DADDROBJ.DBF);
Во-вторых, в основном списке присутствуют нарушения связности, например, ссылки, которые никуда не ведут, т.е. в списке нет элемента или записи с идентификатором, указанном в ссылке. Поэтому не хочется восстанавливать ошибки, которые уже один раз исправлены.
В-третьих, не хочется каждый раз работать с полным список адресообразующих элементов ФИАС, а лишь загружать изменения, которые появляются на официальном сайте Федеральной Налоговой Службы два –три раза в неделю.
Поэтому в процессе загрузки обновления ФИАС используется две временных таблицы:

fias_AddressObjects_temp – для обновлений основного списка адресообразующих элементов;
fias_DeletedAddressObjects_temp – для записей, которые должны быть удалены из основного списка.

Обе таблицы имеют туже структуру, что и основная таблица fias_AddressObjects и создаются той же последовательностью операторов PL/pgSQL, в которой название основной таблицы заменена на временную.

Рис. 8. Предварительная загрузка адресообразующих элементов во временные таблицы.

Последовательность операторов создания основной таблицы приведена в разделе «Создание таблицы адресообразующих элементов ФИАС fias_AddressObjects».
Далее данные таблицы fias_AddressObjects_temp служат для замены (UPDATE) значений в уже существующих записях и добавления (INSERT) вновь созданных записей основную таблицу. С подробным текстом этих операторов можно ознакомиться в разделе «Загрузка обновлений адресообразующих элементов ФИАС в таблицу fias_AddressObjects».

Так как в процессе обновления могут быть внесены нарушения целостности, можно загрузить записи, в которых ссылки на следующую (NEXTID) или предыдущую (PREVID) запись истории указывают на несуществующую запись.
Эта ситуация очень вероятна. Вот, например, данные по результатам загрузки полной базы данных по состоянию на 10.10.2016 года.
Всего нарушений

в значениях NEXTID
- по всем записям — 41109,
- по записях Красноярского края – 1152
в значениях PREVID
- по всем записям — 18577,
- по записях Красноярского края – 981.

Поэтому прежде чем выполнять обновления основной таблицы необходимо отключить действие ограничений:

ALTER   TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_PREVID; 
ALTER   TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_NEXTID.

1 2	ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_PREVID; ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_NEXTID.

После того как обновления основной таблицы выполнены, необходимо присвоить значения NULL полям NEXTID или PREVID там, где их значения указывают на несуществующую запись. Например, так:

UPDATE fias_AddressObjects ao SET NEXTID=NULL
           WHERE ao.NEXTID IS NOT NULL
               AND NOT EXISTS(SELECT * FROM fias_AddressObjects nao
                                       WHERE nao.AOID=ao.NEXTID);
UPDATE fias_AddressObjects ao SET PREVID=NULL   
          WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects pao  
                                       WHERE pao.AOID=ao.PREVID);

UPDATE fias_AddressObjects ao SET NEXTID=NULL

WHERE ao.NEXTID IS NOT NULL

AND NOT EXISTS(SELECT * FROM fias_AddressObjects nao

WHERE nao.AOID=ao.NEXTID);

UPDATE fias_AddressObjects ao SET PREVID=NULL

WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects pao

WHERE pao.AOID=ao.PREVID);

Перед завершением загрузки следует восстановить ограничения и удалить временные таблицы.

Создание функции fstf_AddressObjects_AddressObjectTree

Исходный код функции здесь

BEGIN TRANSACTION;
DROP   FUNCTION IF EXISTS fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER);
CREATE OR REPLACE FUNCTION fstf_AddressObjects_AddressObjectTree(
   a_AOGUID   VARCHAR(36),
   a_CurrStatus   INTEGER default NULL
)
RETURNS TABLE (rtf_AOGUID VARCHAR(36), rtf_CurrStatus INTEGER, rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS
$BODY$
DECLARE
   c_ActualStatusCode   CONSTANT INTEGER :=1;
   c_NotActualStatusCode   CONSTANT INTEGER :=0;
   v_AOGUID      VARCHAR(36);
   v_ParentGUID      VARCHAR(36);
   v_CurrStatus      INTEGER;
   v_ActStatus      INTEGER;
   v_AOLevel      INTEGER;
   v_ShortName      VARCHAR(10);
   v_FormalName      VARCHAR(120);
   v_Return_Error      Integer :=0;
 BEGIN

   IF a_CurrStatus IS NOT NULL THEN
      SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName
            ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName
         FROM fias_AddressObjects ao
         WHERE ao.AOGUID=a_AOGUID AND ao.CurrStatus=a_CurrStatus;

   ELSE
      SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName
            ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName
         FROM fias_AddressObjects ao
         WHERE ao.AOGUID=a_AOGUID AND ao.ActStatus=c_ActualStatusCode;
      IF NOT FOUND THEN
         SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName
               ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName
            FROM fias_AddressObjects ao
            WHERE ao.AOGUID=a_AOGUID AND ao.ActStatus=c_NotActualStatusCode
               AND ao.currstatus = (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                        WHERE ao.aoguid = iao.aoguid);
      END IF;   
   END IF;
   RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName;
   WHILE  v_ParentGUID IS NOT NULL LOOP
      SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName
            ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName
         FROM fias_AddressObjects ao
         WHERE ao.AOGUID=v_ParentGUID AND ao.ActStatus=c_ActualStatusCode;
      IF NOT FOUND THEN
         SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName
               ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName
            FROM fias_AddressObjects ao
            WHERE ao.AOGUID=v_ParentGUID AND ao.ActStatus=c_NotActualStatusCode
               AND ao.currstatus = (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                        WHERE ao.aoguid = iao.aoguid);
      END IF;   
      RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName;
   END LOOP;
END;
  $BODY$
 LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER)
               IS 'Возвращает дерево (список взаимосвязанных строк) с характеристиками адресообразующего элемента';


--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821',51) ORDER BY rtf_AOLevel;
SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821') ORDER BY rtf_AOLevel;

BEGIN TRANSACTION;

DROP FUNCTION IF EXISTS fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER);

CREATE OR REPLACE FUNCTION fstf_AddressObjects_AddressObjectTree(

a_AOGUID VARCHAR(36),

a_CurrStatus INTEGER default NULL

)

RETURNS TABLE (rtf_AOGUID VARCHAR(36), rtf_CurrStatus INTEGER, rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS

$BODY$

DECLARE

c_ActualStatusCode CONSTANT INTEGER :=1;

c_NotActualStatusCode CONSTANT INTEGER :=0;

v_AOGUID VARCHAR(36);

v_ParentGUID VARCHAR(36);

v_CurrStatus INTEGER;

v_ActStatus INTEGER;

v_AOLevel INTEGER;

v_ShortName VARCHAR(10);

v_FormalName VARCHAR(120);

v_Return_Error Integer :=0;

BEGIN

IF a_CurrStatus IS NOT NULL THEN

SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName

ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName

FROM fias_AddressObjects ao

WHERE ao.AOGUID=a_AOGUID AND ao.CurrStatus=a_CurrStatus;

ELSE

SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName

ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName

FROM fias_AddressObjects ao

WHERE ao.AOGUID=a_AOGUID AND ao.ActStatus=c_ActualStatusCode;

IF NOT FOUND THEN

SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName

ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName

FROM fias_AddressObjects ao

WHERE ao.AOGUID=a_AOGUID AND ao.ActStatus=c_NotActualStatusCode

AND ao.currstatus = (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao

WHERE ao.aoguid = iao.aoguid);

END IF;

RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName;

WHILE v_ParentGUID IS NOT NULL LOOP

SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName

ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName

FROM fias_AddressObjects ao

WHERE ao.AOGUID=v_ParentGUID AND ao.ActStatus=c_ActualStatusCode;

IF NOT FOUND THEN

SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName

ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,ao.ShortName,ao.FormalName

FROM fias_AddressObjects ao

WHERE ao.AOGUID=v_ParentGUID AND ao.ActStatus=c_NotActualStatusCode

AND ao.currstatus = (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao

WHERE ao.aoguid = iao.aoguid);

END IF;

RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,v_FormalName;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql;

COMMENT ON FUNCTION fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER)

IS 'Возвращает дерево (список взаимосвязанных строк) с характеристиками адресообразующего элемента';

--ROLLBACK TRANSACTION;

COMMIT TRANSACTION;

SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821',51) ORDER BY rtf_AOLevel;

SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821') ORDER BY rtf_AOLevel;

Создание функции fsfn_AddressObjects_ObjectGroup

Исходный код функции здесь

BEGIN TRANSACTION;
DROP   FUNCTION IF EXISTS fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER);
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_ObjectGroup(
      a_AOGUID   VARCHAR(36),
      a_CurrStatus   INTEGER default NULL
                        
)
RETURNS VARCHAR(50)
AS
$BODY$
DECLARE
   c_CountryGroupValue      CONSTANT VARCHAR(50):='Country';
   c_RegionGroupValue      CONSTANT VARCHAR(50):='Region';
   c_CityGroupValue      CONSTANT VARCHAR(50):='City';
   c_TerritoryGroupValue      CONSTANT VARCHAR(50):='Territory';
   c_LocalityGroupValue      CONSTANT VARCHAR(50):='Locality';
   c_MotorRoadValue      CONSTANT VARCHAR(50):='MotorRoad';
   c_RailWayObjectValue      CONSTANT VARCHAR(50):='RailWayObject';
   c_VillageCouncilValue      CONSTANT VARCHAR(50):='VillageCouncil';
   c_StreetGroupValue      CONSTANT VARCHAR(50):='Street';
   c_AddlTerritoryValue      CONSTANT VARCHAR(50):='AddlTerritory';
   c_PartAddlTerritoryValue   CONSTANT VARCHAR(50):='PartAddlTerritory';
   
   v_ShortTypeName      VARCHAR(10);
   v_AddressObjectName   VARCHAR(100);
   v_AOLevel      INTEGER;
   v_CurrStatus      INTEGER;
   v_ObjectGroup      VARCHAR(50);
    v_Return_Error      Integer :=0;
 BEGIN
    SELECT INTO v_CurrStatus COALESCE(a_CurrStatus,MIN(addrobj.currstatus)) FROM fias_AddressObjects addrobj WHERE addrobj.AOGUID=a_AOGUID;
   SELECT INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel ShortName,FormalName,AOLevel FROM fias_AddressObjects addrobj   
                  WHERE addrobj.AOGUID=a_AOGUID AND addrobj.currstatus = v_CurrStatus    
                  LIMIT 1;
   IF v_AOLevel = 1 AND UPPER(v_ShortTypeName) &lt;&gt; 'Г' THEN
      v_ObjectGroup:=c_RegionGroupValue;
   ELSIF v_AOLevel = 1 AND UPPER(v_ShortTypeName) =  'Г' THEN 
      v_ObjectGroup:=c_CityGroupValue;
   ELSIF v_AOLevel = 3  THEN
      v_ObjectGroup:=c_TerritoryGroupValue;
   ELSIF (v_AOLevel = 4 AND UPPER(v_ShortTypeName) NOT IN ('С/С','С/А','С/О','С/МО'))  OR (v_AOLevel = 1 AND UPPER(v_ShortTypeName) &lt;&gt; 'Г') THEN 
      v_ObjectGroup:=c_CityGroupValue;
   ELSIF v_AOLevel IN (4,6)  AND UPPER(v_ShortTypeName) IN ('С/С','С/А','С/О','С/МО') AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN  
      v_ObjectGroup:=c_VillageCouncilValue;   
   ELSIF v_AOLevel = 6 AND UPPER(v_ShortTypeName) NOT IN ('С/С','С/А','С/О','С/МО','САД','СНТ','ТЕР','АВТОДОРОГА','ПРОМЗОНА','ДП','МКР')
            AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN 
      v_ObjectGroup:=c_LocalityGroupValue;
   ELSIF  UPPER(v_ShortTypeName) IN ('АВТОДОРОГА') THEN 
      v_ObjectGroup:=c_MotorRoadValue;
   ELSIF  v_AOLevel IN (6,7) AND UPPER(v_ShortTypeName) LIKE ('Ж/Д%') THEN
      v_ObjectGroup:=c_RailWayObjectValue;   
   ELSIF v_AOLevel = 7 AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') AND UPPER(v_ShortTypeName) NOT IN ('УЧ-К','ГСК','ПЛ-КА','СНТ','ТЕР') 
         OR (v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('МКР') )  THEN
      v_ObjectGroup:=c_StreetGroupValue;
   ELSIF v_AOLevel = 90 OR v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('САД','СНТ','ТЕР','ПРОМЗОНА','ДП')
      OR v_AOLevel = 7 AND UPPER(v_ShortTypeName) IN ('УЧ-К','ГСК','ПЛ-КА','СНТ','ТЕР')  THEN
      v_ObjectGroup:=c_AddlTerritoryValue;
   ELSIF v_AOLevel = 91 THEN
      v_ObjectGroup:=c_PartAddlTerritoryValue;
   END IF;   

   RETURN v_ObjectGroup;
  END;
  $BODY$
 LANGUAGE plpgsql;
COMMENT ON FUNCTION fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER)
               IS 'Возвращает  признак группы адресного объекта по его идентификатору в таблице fias_AddressObjects';


--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECT fsfn_AddressObjects_ObjectGroup('719b789d-2476-430a-89cd-3fedc643d821',51);
SELECT fsfn_AddressObjects_ObjectGroup('db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1');
SELECT fsfn_AddressObjects_ObjectGroup('625497d3-22de-4390-b4b4-2febfbfc15ce');
SELECT fsfn_AddressObjects_ObjectGroup('39da6405-b3e6-4baf-b332-d47b73b4d5fb');
SELECT fsfn_AddressObjects_ObjectGroup('bfc1236d-b5d2-4734-a238-3b1e4830e963');

BEGIN TRANSACTION;

DROP FUNCTION IF EXISTS fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER);

CREATE OR REPLACE FUNCTION fsfn_AddressObjects_ObjectGroup(

a_AOGUID VARCHAR(36),

a_CurrStatus INTEGER default NULL

)

RETURNS VARCHAR(50)

$BODY$

DECLARE

c_CountryGroupValue CONSTANT VARCHAR(50):='Country';

c_RegionGroupValue CONSTANT VARCHAR(50):='Region';

c_CityGroupValue CONSTANT VARCHAR(50):='City';

c_TerritoryGroupValue CONSTANT VARCHAR(50):='Territory';

c_LocalityGroupValue CONSTANT VARCHAR(50):='Locality';

c_MotorRoadValue CONSTANT VARCHAR(50):='MotorRoad';

c_RailWayObjectValue CONSTANT VARCHAR(50):='RailWayObject';

c_VillageCouncilValue CONSTANT VARCHAR(50):='VillageCouncil';

c_StreetGroupValue CONSTANT VARCHAR(50):='Street';

c_AddlTerritoryValue CONSTANT VARCHAR(50):='AddlTerritory';

c_PartAddlTerritoryValue CONSTANT VARCHAR(50):='PartAddlTerritory';

v_ShortTypeName VARCHAR(10);

v_AddressObjectName VARCHAR(100);

v_AOLevel INTEGER;

v_CurrStatus INTEGER;

v_ObjectGroup VARCHAR(50);

v_Return_Error Integer :=0;

BEGIN

SELECT INTO v_CurrStatus COALESCE(a_CurrStatus,MIN(addrobj.currstatus)) FROM fias_AddressObjects addrobj WHERE addrobj.AOGUID=a_AOGUID;

SELECT INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel ShortName,FormalName,AOLevel FROM fias_AddressObjects addrobj

WHERE addrobj.AOGUID=a_AOGUID AND addrobj.currstatus = v_CurrStatus

LIMIT 1;

IF v_AOLevel = 1 AND UPPER(v_ShortTypeName) <> 'Г' THEN

v_ObjectGroup:=c_RegionGroupValue;

ELSIF v_AOLevel = 1 AND UPPER(v_ShortTypeName) = 'Г' THEN

v_ObjectGroup:=c_CityGroupValue;

ELSIF v_AOLevel = 3 THEN

v_ObjectGroup:=c_TerritoryGroupValue;

ELSIF (v_AOLevel = 4 AND UPPER(v_ShortTypeName) NOT IN ('С/С','С/А','С/О','С/МО')) OR (v_AOLevel = 1 AND UPPER(v_ShortTypeName) <> 'Г') THEN

v_ObjectGroup:=c_CityGroupValue;

ELSIF v_AOLevel IN (4,6) AND UPPER(v_ShortTypeName) IN ('С/С','С/А','С/О','С/МО') AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN

v_ObjectGroup:=c_VillageCouncilValue;

ELSIF v_AOLevel = 6 AND UPPER(v_ShortTypeName) NOT IN ('С/С','С/А','С/О','С/МО','САД','СНТ','ТЕР','АВТОДОРОГА','ПРОМЗОНА','ДП','МКР')

AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN

v_ObjectGroup:=c_LocalityGroupValue;

ELSIF UPPER(v_ShortTypeName) IN ('АВТОДОРОГА') THEN

v_ObjectGroup:=c_MotorRoadValue;

ELSIF v_AOLevel IN (6,7) AND UPPER(v_ShortTypeName) LIKE ('Ж/Д%') THEN

v_ObjectGroup:=c_RailWayObjectValue;

ELSIF v_AOLevel = 7 AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') AND UPPER(v_ShortTypeName) NOT IN ('УЧ-К','ГСК','ПЛ-КА','СНТ','ТЕР')

OR (v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('МКР') ) THEN

v_ObjectGroup:=c_StreetGroupValue;

ELSIF v_AOLevel = 90 OR v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('САД','СНТ','ТЕР','ПРОМЗОНА','ДП')

OR v_AOLevel = 7 AND UPPER(v_ShortTypeName) IN ('УЧ-К','ГСК','ПЛ-КА','СНТ','ТЕР') THEN

v_ObjectGroup:=c_AddlTerritoryValue;

ELSIF v_AOLevel = 91 THEN

v_ObjectGroup:=c_PartAddlTerritoryValue;

END IF;

RETURN v_ObjectGroup;

END;

$BODY$

LANGUAGE plpgsql;

COMMENT ON FUNCTION fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER)

IS 'Возвращает признак группы адресного объекта по его идентификатору в таблице fias_AddressObjects';

--ROLLBACK TRANSACTION;

COMMIT TRANSACTION;

SELECT fsfn_AddressObjects_ObjectGroup('719b789d-2476-430a-89cd-3fedc643d821',51);

SELECT fsfn_AddressObjects_ObjectGroup('db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1');

SELECT fsfn_AddressObjects_ObjectGroup('625497d3-22de-4390-b4b4-2febfbfc15ce');

SELECT fsfn_AddressObjects_ObjectGroup('39da6405-b3e6-4baf-b332-d47b73b4d5fb');

SELECT fsfn_AddressObjects_ObjectGroup('bfc1236d-b5d2-4734-a238-3b1e4830e963');

Создание функции fsfn_AddressObjects_TreeActualName

Исходный код функции здесь

BEGIN TRANSACTION;
DROP   FUNCTION IF EXISTS fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_TreeActualName(
       a_AOGUID      VARCHAR(36) DEFAULT NULL, 
       a_MaskArray      VARCHAR(2)[10] default '{TP,LM,LP,ST}'
)
RETURNS VARCHAR(1000) AS
$BODY$
DECLARE
   c_CountryGroupValue      CONSTANT VARCHAR(50):='Country';
   c_RegionGroupValue      CONSTANT VARCHAR(50):='Region';
   c_CityGroupValue      CONSTANT VARCHAR(50):='City';
   c_TerritoryGroupValue      CONSTANT VARCHAR(50):='Territory';
   c_LocalityGroupValue      CONSTANT VARCHAR(50):='Locality';
   c_MotorRoadValue      CONSTANT VARCHAR(50):='MotorRoad';
   c_RailWayObjectValue      CONSTANT VARCHAR(50):='RailWayObject';
   c_VillageCouncilValue      CONSTANT VARCHAR(50):='VillageCouncil';
   c_StreetGroupValue      CONSTANT VARCHAR(50):='Street';
   c_AddlTerritoryValue      CONSTANT VARCHAR(50):='AddlTerritory';
   c_PartAddlTerritoryValue   CONSTANT VARCHAR(50):='PartAddlTerritory';
   c_StreetMask       CONSTANT  VARCHAR(2)[1] :='{ST}';
   c_PostIndexMask      CONSTANT  VARCHAR(2)[1] :='{ZC}';
   c_DistrictMask      CONSTANT  VARCHAR(2)[1] :='{DT}';
   c_PartLocalityMask   CONSTANT  VARCHAR(2)[1] :='{LP}';
   c_MainLocalityMask   CONSTANT  VARCHAR(2)[1] :='{LM}';
   c_PartTerritoryMask   CONSTANT  VARCHAR(2)[1] :='{TP}';
   c_MainTerritoryMask   CONSTANT  VARCHAR(2)[1] :='{TM}';
   c_CountryMask      CONSTANT  VARCHAR(2)[1] :='{CY}';
   v_ShortTypeName      VARCHAR(10);
   v_AddressObjectName   VARCHAR(100);
   v_AOLevel      INTEGER;
   v_MinCurrStatus      INTEGER;
   v_TreeAddressObjectName   VARCHAR(1000);
   v_ObjectGroup      VARCHAR(50);         
   v_TreeLeverCount   INTEGER;
   v_Return_Error_i    Integer := 0;
   cursor_AddressObjectTree RefCURSOR; 
   v_Return_Error      Integer :=0;
 BEGIN
   SELECT INTO v_MinCurrStatus   MIN(addrobj.currstatus) FROM fias_AddressObjects addrobj     WHERE aoguid=a_AOGUID;
   OPEN cursor_AddressObjectTree FOR SELECT rtf_ShortTypeName,REPLACE(rtf_AddressObjectName,'  ',' '),
                     rtf_AOLevel,fsfn_AddressObjects_ObjectGroup(rtf_AOGUID )
                  FROM fstf_AddressObjects_AddressObjectTree(a_AOGUID) ORDER BY rtf_AOLevel;
               
   v_TreeLeverCount:=0;
   v_TreeAddressObjectName:='';
   FETCH FIRST FROM cursor_AddressObjectTree INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel,v_ObjectGroup;
   WHILE FOUND
   LOOP
      v_TreeLeverCount:=v_TreeLeverCount+1;   
RAISE INFO 'v_ShortTypeName=%,v_AddressObjectName=%,v_AOLevel=%,v_ObjectGroup=% ?=%',v_ShortTypeName,v_AddressObjectName,v_AOLevel,v_ObjectGroup,UPPER(v_ShortTypeName) LIKE UPPER('%Респ%');

      IF v_ObjectGroup=c_CountryGroupValue AND c_CountryMask &lt;@ a_MaskArray AND v_AOLevel =0 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                        v_AddressObjectName||' '||v_ShortTypeName;
      ELSIF v_ObjectGroup=c_RegionGroupValue AND c_MainTerritoryMask &lt;@ a_MaskArray  AND v_AOLevel &lt;=2 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                        CASE WHEN UPPER(v_ShortTypeName) LIKE UPPER('%Респ%') THEN 'Республика ' ||
                                                v_AddressObjectName
                           ELSE v_AddressObjectName||' '||v_ShortTypeName END;
      ELSIF v_ObjectGroup=c_TerritoryGroupValue AND c_PartTerritoryMask &lt;@ a_MaskArray AND v_AOLevel =3 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                              v_AddressObjectName||' '||v_ShortTypeName;
      ELSIF v_ObjectGroup=c_CityGroupValue AND c_MainLocalityMask &lt;@ a_MaskArray AND v_AOLevel =4 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                              CASE WHEN UPPER(LEFT(v_AddressObjectName,6+LENGTH(v_ShortTypeName)))
                                       ='ЗАТО '||UPPER(TRIM(v_ShortTypeName))||'.' 
                                    THEN v_AddressObjectName
                                    ELSE v_ShortTypeName ||' '|| v_AddressObjectName END;
      ELSIF v_ObjectGroup=c_LocalityGroupValue AND c_DistrictMask &lt;@ a_MaskArray AND v_AOLevel =5 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                              v_AddressObjectName||' '||v_ShortTypeName ;
      ELSIF v_ObjectGroup=c_LocalityGroupValue AND c_PartLocalityMask &lt;@ a_MaskArray AND v_AOLevel =6 THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                              v_ShortTypeName ||' '|| v_AddressObjectName;
      ELSIF v_ObjectGroup=c_StreetGroupValue AND c_StreetMask &lt;@ a_MaskArray AND v_AOLevel =7  THEN
         v_TreeAddressObjectName:=v_TreeAddressObjectName||CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' END ||
                              v_ShortTypeName ||' '|| v_AddressObjectName;
      END IF;
      FETCH NEXT  FROM cursor_AddressObjectTree INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel,v_ObjectGroup;
   END LOOP;
   CLOSE cursor_AddressObjectTree;
    RETURN    v_TreeAddressObjectName;
  END;
  $BODY$
LANGUAGE plpgsql ;
COMMENT ON FUNCTION fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10])
               IS 'Возвращает  строку с полным названием адресообразующего элемента';


--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963','{TM,TP,LM,LP,ST}');
SELECT fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963');

BEGIN TRANSACTION;

DROP FUNCTION IF EXISTS fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;

CREATE OR REPLACE FUNCTION fsfn_AddressObjects_TreeActualName(

a_AOGUID VARCHAR(36) DEFAULT NULL,

a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST}'

)

RETURNS VARCHAR(1000) AS

$BODY$

DECLARE

c_CountryGroupValue CONSTANT VARCHAR(50):='Country';

c_RegionGroupValue CONSTANT VARCHAR(50):='Region';

c_CityGroupValue CONSTANT VARCHAR(50):='City';

c_TerritoryGroupValue CONSTANT VARCHAR(50):='Territory';

c_LocalityGroupValue CONSTANT VARCHAR(50):='Locality';

c_MotorRoadValue CONSTANT VARCHAR(50):='MotorRoad';

c_RailWayObjectValue CONSTANT VARCHAR(50):='RailWayObject';

c_VillageCouncilValue CONSTANT VARCHAR(50):='VillageCouncil';

c_StreetGroupValue CONSTANT VARCHAR(50):='Street';

c_AddlTerritoryValue CONSTANT VARCHAR(50):='AddlTerritory';

c_PartAddlTerritoryValue CONSTANT VARCHAR(50):='PartAddlTerritory';

c_StreetMask CONSTANT VARCHAR(2)[1] :='{ST}';

c_PostIndexMask CONSTANT VARCHAR(2)[1] :='{ZC}';

c_DistrictMask CONSTANT VARCHAR(2)[1] :='{DT}';

c_PartLocalityMask CONSTANT VARCHAR(2)[1] :='{LP}';

c_MainLocalityMask CONSTANT VARCHAR(2)[1] :='{LM}';

c_PartTerritoryMask CONSTANT VARCHAR(2)[1] :='{TP}';

c_MainTerritoryMask CONSTANT VARCHAR(2)[1] :='{TM}';

c_CountryMask CONSTANT VARCHAR(2)[1] :='{CY}';

v_ShortTypeName VARCHAR(10);

v_AddressObjectName VARCHAR(100);

v_AOLevel INTEGER;

v_MinCurrStatus INTEGER;

v_TreeAddressObjectName VARCHAR(1000);

v_ObjectGroup VARCHAR(50);

v_TreeLeverCount INTEGER;

v_Return_Error_i Integer := 0;

cursor_AddressObjectTree RefCURSOR;

v_Return_Error Integer :=0;

BEGIN

SELECT INTO v_MinCurrStatus MIN(addrobj.currstatus) FROM fias_AddressObjects addrobj WHERE aoguid=a_AOGUID;

OPEN cursor_AddressObjectTree FOR SELECT rtf_ShortTypeName,REPLACE(rtf_AddressObjectName,' ',' '),

rtf_AOLevel,fsfn_AddressObjects_ObjectGroup(rtf_AOGUID )

FROM fstf_AddressObjects_AddressObjectTree(a_AOGUID) ORDER BY rtf_AOLevel;

v_TreeLeverCount:=0;

v_TreeAddressObjectName:='';

FETCH FIRST FROM cursor_AddressObjectTree INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel,v_ObjectGroup;

WHILE FOUND

LOOP

v_TreeLeverCount:=v_TreeLeverCount+1;

RAISE INFO 'v_ShortTypeName=%,v_AddressObjectName=%,v_AOLevel=%,v_ObjectGroup=% ?=%',v_ShortTypeName,v_AddressObjectName,v_AOLevel,v_ObjectGroup,UPPER(v_ShortTypeName) LIKE UPPER('%Респ%');

IF v_ObjectGroup=c_CountryGroupValue AND c_CountryMask <@ a_MaskArray AND v_AOLevel =0 THEN