10 October 2018

Дома ФИАС в среде PostgreSQL

PostgreSQL
Предыдущая статья с описанием адресов ФИАС и функций для работы с ними в среде PostgreSQL вызвала интерес у небольшой части читателей.

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


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

Эта статья тесно связана с материалами серии статей «Адреса ФИАС в среде PostgreSQL» (начало,продолжение 1,продолждение 2,окончание).

Родословная дома


Начнем с примера.

Вызов функции fstf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') приведет к получению следующего списка записей.


Таблица 1. Результат выполнения функции.



При внимательном рассмотрении можно заметить, что в качестве аргумента функции передан идентификатор элемента (HOUSEGUID) «д. 1, корп. 2, стр. 26», в результате получены шесть записей:


  • три родительских записи с адресообразующими элементами: о крае, городе и улице;
  • три записи с характеристиками номера дома: номер дома, номер корпуса и номер строения.

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

Полный текст функции приведен в Приложении в подразделе Создание функции fstf_Houses_AddressObjectTree.


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


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


При всей внешней простоте механизма взаимодействия списка домов со списком адресообразующих элементов в их взаимодействии особенности, осложняющие реализацию функций на HOUSES.


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


Во-вторых, в списке ФИАС несколько записей с одним и тем же набором характеристик номера дома: номер дома, номер корпуса, номер строения.


В-третьих, запись о доме не всегда наследуется от записи об улице населенного пункта.


Но, обо всем по порядку.

Для дальнейшего рассмотрения хранения сведений о домах в ФИАС достаточно ограничиться на 4 таблицах (DBF-файлах):




  • ADDROBJ – список адресообразующих элементов;
  • HOUSES – список домов;
  • STRSTAT – справочник признаков строения;
  • ESTSTAT – справочник признаков владения.

ADDROBJ подробно рассмотрена в предыдущей публикации «Адреса ФИАС в PostgreSQL», поэтому здесь о ее особенностях будет говорится ровно столько, сколько необходимо для описания характеристик домов.


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




Как видно из таблицы, в отличии от адресообразующих объектов, записи истории дома не имеют специальных признаков актуальности. Актуальной является запись с самой старшей датой окончания периода, которая больше текущей. Пока актуальные записи о домах помечаются датой «06.06.2079». Все остальные записи о доме считаются историческими, а даты начала и окончания характеризуют период актуальности каждой записи.


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



SELECT *  FROM fias_Houses h 
               WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21'
               ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;

Внимательный читатель, глядя на Рис. 1, наверняка задал себе вопрос: зачем упомянуты справочники признаков строения и владения? В ФИАС используется свыше 10 подобного рода справочников, так почему особо выделены эти два?


Ответ многих удивит – с точки зрения «логики ФИАС» адрес дома не полностью идентифицируется адресом улицы, номерами дома, корпуса и строения. Термин «логики ФИАС» использован в ответе сотрудника ФНС на мой вопрос почему в списке домов Красноярского края находится свыше 250 парных адресов домов. В этом же ответе было сказано, что уникальность записи обеспечивают значения AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.





Другими словами, для нахождения объекта недостаточно знать населенных пункт, улицу, номер дома. Необходимо еще знать:


  • «владение» это или «домовладение»;
  • определен статус этого объекта или не определен;
  • и т.д.



Вот так выглядит выборка из общего списка домов ФИАС с повторяющимися адресами.
В том, что разные объекты имеют одинаковый адрес нет ничего удивительного. Здание и земельный участок под ним; дом, гараж, баня у одного хозяина. Все они имеют один и тот же адрес. Но ФИАС – это адресный реестр, т.е. список адресов. Поэтому естественно ожидать, что уникальными в нем будут адреса, а не здания, строения, сооружения.


Т.е. список домов ФИАС из списка адресов домов начал развиваться в сторону перечня наземных строений. И пользователям ФИАС необходимо это учитывать.


Каждый желающий может сам проверить наличие домов с повторяющимися адресами, выполнив оператор SELECT подобный следящему. При этом, функцию fsfn_Houses_TreeActualName можно не применять, т.к. она использована только для того, чтобы сократить число колонок результата. Не обязательно использовать справочники fias_StructureStatus (аналог STRSTAT) и fias_EstateStatus (аналог ESTSTAT), т.к. отмеченный эффект можно проследить и на кодах признаков строения и владения.




исходный код оператора
SELECT fsfn_Houses_TreeActualName(h.AOGUID,h.HOUSEGUID),h.HOUSEGUID,str.StructureStatusName,est.EstateStatusName
FROM fias_Houses h
INNER JOIN (SELECT  AOGUID,HOUSENUM,BUILDNUM,STRUCNUM,COUNT(*)
	FROM fias_Houses h 
	WHERE EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') 
	GROUP BY AOGUID,HOUSENUM,BUILDNUM,STRUCNUM
	HAVING COUNT(*)>1) hg 
		ON h.AOGUID=hg.AOGUID AND h.HOUSENUM=hg.HOUSENUM
		AND COALESCE(h.BUILDNUM,'')=COALESCE(hg.BUILDNUM,'')
 AND COALESCE(h.STRUCNUM,'')=COALESCE(hg.STRUCNUM,'')
LEFT OUTER JOIN fias_StructureStatus str ON h.STRSTATUS=str.StructureStatusID
LEFT OUTER JOIN fias_EstateStatus est ON h.ESTSTATUS=est.EstateStatusID
WHERE h.EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')
ORDER BY h.AOGUID,h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,h.STRSTATUS,h.ESTSTATUS;


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


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


  • 98% домов ФИАС связаны с улицами в населенных пунктах;
  • 1,2% домов¬ — с улицами в садово-огородных товариществах;
  • 0,3% домов- с населенными пунктами;
  • 0,5% домов- с прочими адресными элементами.


Рис. 2.

Размножение адресов дома по владельцам (ФИАС vs карта)


Здесь описана проблема, которая приводит к неоднозначному толкованию родословной дома. (На эту проблему мое внимание обратил Игорь Леонидович Тимощенков, ГИС-специалист, компании ООО «Айгео», Красноярск).


Выше показано как несколько записей содержат один и тот же адрес дома. Что можно объяснить желанием налоговой инспекции хранить не только запись о частном доме, но и об окружающих постройках: гараже, сарае, и т.д. Но есть обратные примеры, когда одному строению (дому) соответствуют несколько записей таблицы fias_Houses с различными значениями номеров этого дома.





Вглядитесь в этот рисунок. Слева на нем снимок экрана с картой поселка, где расположены домами на два хозяина. Это обычные одноэтажные дома с двумя входами. Справа живет одна семья, а слева другая. Их еще можно представлять, как дома из двух квартир.


А теперь посмотрите на таблицу справа. В ней практически каждому дому на два хозяина соответствует по 3 записи. Т.е. таблице домов ФИАС указаны как адрес отдельного дома («д. 1»), так и адреса частей дома («д. 1/1», «д. 1/2»), принадлежащих одному хозяину.


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


Функция fstf_Houses_AddressObjectTree имеет две версии: с четырьмя или с двумя параметрами. В версии функции с двумя параметрам передается идентификатор дома (HouseGUID) и дата окончания действия записи (EndDate). Версия с четырьмя параметрами дополнительно требует идентификатор адресообразующего элемента (AOGUID) и текущий статус (CurrStatus).




исходный код оператора
SELECT INTO v_AOGUID,v_CurrStatus
                h.AOGUID,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
    FROM fias_Houses h
        INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID
    WHERE h.HOUSEGUID=a_HOUSEGUID
            AND h.ENDDATE=
         COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS'))
    ORDER BY h.ENDDATE DESC;


Функция с меньшим числом параметров вычисляет значения недостающих параметров и вызывает функцию с большим числом параметров. Для этого предварительно идентификатор адресообразующего элемента просто извлекается из соответствующего поля таблицы домов (fias_Houses). А значение текущего статуса (CurrStatus) вычисляется по следующим правилам:


  • если ни одна из записей истории адресообразующего элемента не содержит 0 в поле CurrStatus, то переменной v_CurrStatus присваивается максимальное значение поля для этого адресообразующего элемента;
  • иначе этой переменной присваивается значение 0.

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

.

Затем записи об адресообразующих элементах дополняются записями о номерах дома, корпуса, строения (см. Таблица 1), которые создаются для каждого непустого поля о номере дома, корпуса и строения.


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


Коду уровня дома (корпуса, строения) всегда присваивается значение 8, смотри справочник «Уровней адресных объектов" из документа Сведения о составе информации ФИАС).


Статусу актуальности присваивается значение 1, если дата окончания действия записи (EndDate) равна 06.06.2079, и 0 в противном случае.


Со значениями поля CurrStatus дело обстоит сложнее. При помощи его значений решаются две задачи одновременно: устанавливается идентификатор каждой версии записи об адресообразующем элементе и присваивается признак актуальности записи. Поэтому последняя актуальная запись об элементе содержит значение 0 в этом поле, а все исторические записи нумеруются в порядке появления – «1» самая ранняя запись, следующая за ней по времени – «2» и т.д. Подробнее порядок присвоения значений полю CurrStatus рассмотрен в публикации «Адреса ФИАС в PostgreSQL».




Заголовок спойлера
SELECT h.AOGUID, h.HOUSEGUID, h.HOUSENUM, h.BUILDNUM, h.STRUCNUM, h.ENDDATE,
    CASE WHEN COALESCE(h.ENDDATE, 
                        TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS'))
             =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 0
        ELSE RANK() OVER (PARTITION BY h.AOGUID, h.HOUSEGUID ORDER BY h.ENDDATE ASC)
    END AS HouseCurrStatus,
    CASE WHEN COALESCE (h.ENDDATE, 
                        TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS'))
              =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 1
        ELSE 0 
    END AS HouseActStatus
  FROM fias_Houses h
        INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID
  WHERE h.AOGUID=a_AOGUID AND h.HOUSEGUID=a_HOUSEGUID
        AND h.ENDDATE=
            COALESCE(a_ENDDATE, 
                    TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS'))
  ORDER BY h.ENDDATE DESC;


Полный адрес дома


Основная идея функции fsfn_Houses_TreeActualName в том, чтобы возвратить соединенные в одну строку номер дома вместе с названиями всех его предков – адресообразующих элементов.


Например, пусть функция родословной дома (fstf_Houses_AddressObjectTree) возвращает следующий список значений.


Таблица 4. Результат выполнения функции fstf_Houses_AddressObjectTree('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')



Тогда fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') должна возвратить: «г Красноярск, ул им Сергея Лазо, д. 34А, корп. 6, стр. 17».


Функцию fsfn_Houses_TreeActualName упрощенно можно представлять, как агрегатную функцию STRING_AGG над результатом функции, возвращающей родословную дома.


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


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



текстовый вариант таблицы
Значение Примечание
{HS} Маска — номер дома
{BY} Маска — номер корпуса
{BG} Маска — номер строения
{ST} Маска — улица
{ZC} Маска — почтовый индекс
{DT} Маска — городской район
{LP} Маска — подчиненный населенный пункт
{LM} Маска — основной населенный пункт
{TP} Маска — района субъекта федерации
{TM} Маска — субъект федерации (регион)
{CY} Маска — страна


Смотри также раздел «Полное наименование адресообразующего элемента» публикации «Адреса ФИАС в PostgreSQL».
Текст функции приведен в разделе Приложения «Создание функции fsfn_Houses_TreeActualName».

Поиск дома ФИАС


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


Рассмотрим несколько примеров. И для начала найдем все дома с номером «220».


Таблица 6. Результат выполнения функции fstf_Houses_SearchByName('220')



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


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


Таблица 7. Результат выполнения функции fstf_Houses_SearchByName('1',NULL,NULL,'Красноярск')



Назначение остальных параметров в точности совпадает с назначением параметров функции поиска адресообразующих эелементов (fstf_AddressObjects_SearchByName).
Текст функции приведен в разделе Приложения «Создание функции fstf_Houses_SearchByName»

.

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


Реализация fstf_Houses_SearchByName во многом похожа на реализацию функции поиска адресообразующих элементов (fstf_AddressObjects_SearchByName). Главное отличие заключается в том, что поиск осуществляется в двух, связанных таблицах fias_Houses и fias_AddressObjects.


У функции 9 аргументов. Первые три из них – это номера дома (a_HouseNum), корпуса (a_BuildNum) и строения (a_StrucNum). Остальные 6 (a_FormalName, a_ShortName, a_ParentFormalName, a_ParentShortName, a_GrandParentFormalName, a_GrandParentShortName) полностью совпадают с параметрами функции.

Если задать только значение параметра «номер дома», то функция возвратит все адреса в номере дома, которых встречаются указанная последовательность символом. Если в качестве номера дома передать значение NULL или пустую строку («»), то будут возвращены адреса всех домов, адресные элементы которых заданы набором прочих параметров.



Эпилог


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


Загрузка данных в таблицу домов выполняется примерно также как загрузка данных в таблицу адресообразующих элементов. Только исходным файлом станет HOUSE99.DBF, а не ADDROB99.DBF. Здесь 99 – это номер региона (Республики, области, края). Например, для Красноярского края исходным является файл HOUSE24.DBF.


Сначала с страницы «Обновления» ФИАС скачивается очередной архив с обновлением. Из него извлекается файл HOUSE99.DBF

.

Затем файл HOUSE99.DBF преобразуется к формату CSV и уже преобразованный он загружается оператором COPY во временную таблицу fias_Houses_Temp.


И, наконец, данные временной используют для обновления основной таблицы, т.е. несуществующие в fias_Houses добавляются, а уже существующие – заменяются.
Пример скрипта обновления таблицы домов приведен в разделе «Загрузка обновлений домов ФИАС в таблицу fias_Houses».


Приложение


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


Комментарии к исходному коду функции можно посмотреть здесь.

код функции
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID	VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP);
/******************************************************************/
/* Возвращает дерево (список взаимосвязанных строк) с характеристиками */
/* дома и других адресообразующих элементов                                           */
/******************************************************************/	
CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree(
    a_AOGUID VARCHAR(36), /* Глобальный уникальный идентификатор */
                                           /* адресного объекта*/
    a_HOUSEGUID VARCHAR(36),/* Глобальный уникальный идентификатор дома */
    a_CurrStatus INTEGER default 0,/* Статус актуальности КЛАДР 4: */
                                      /* 0 - актуальный, */
                                      /* 1-50 - исторический, т.е. объект */
                                      /*           был переименован,          */
                                      /*          в данной записи приведено*/
                                     /*           одно из прежних     */
                                      /*         его наименований, */
                                      /*         51 - переподчиненный */
    a_ENDDATE TIMESTAMP default '2079-06-06' /* Окончание действия записи о доме*/
)
RETURNS  TABLE (rtf_GUID  VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER,  rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS
$BODY$
DECLARE
    c_HouseAOLevel CONSTANT INTEGER:=8;
    c_HouseShortTypeName CONSTANT VARCHAR(10):='д.';
    c_BuildShortTypeName CONSTANT VARCHAR(10):='корп.';
    c_StructShortTypeName CONSTANT VARCHAR(10):='стр.';
    c_StatusActual CONSTANT INTEGER:=1;	/* Признак актуальности записи */
    c_StatusNotActual CONSTANT INTEGER:=0; /* Признак неактальной записи записи */
    c_MAXENDDATE CONSTANT TIMESTAMP:=to_timestamp('2079-06-06 00:00:00', 
                                                                                      'YYYY-MM-DD');
    v_HouseActStatus 	INTEGER;	/* Признак актуальности для здания*/
    v_HouseCurrStatus INTEGER;	/* Признак актуальности для здания */
    v_ENDDATE TIMESTAMP;	/* Окончание действия записи */
    v_HOUSEGUID VARCHAR(36);	/* Глобальный уникальный идентификатор дома */
    v_HOUSENUM VARCHAR(10);	/* Номер дома */
    v_BUILDNUM VARCHAR(10);	/* Номер корпуса */
    v_STRUCNUM VARCHAR(10);	/* Номер строения */
    v_Return_Error Integer :=0;	/* Код возврата */
--************************************************************       
--************************************************************
 BEGIN
    RETURN QUERY SELECT * FROM fstf_AddressObjects_AddressObjectTree
                                                        (a_AOGUID,a_CurrStatus);
    IF a_ENDDATE IS NULL THEN 
        SELECT INTO v_ENDDATE MAX(ENDDATE) 
                FROM fias_Houses WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID;
    ELSE
        v_ENDDATE:=a_ENDDATE;
    END IF;
    SELECT INTO v_HOUSENUM,v_BUILDNUM,v_STRUCNUM,
                            v_ENDDATE,v_HouseCurrStatus
                    h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,
                            h.ENDDATE,ah.HouseCurrStatus
        FROM fias_Houses h
            INNER JOIN (SELECT AOGUID,HOUSEGUID,ENDDATE, 
                           RANK() OVER (PARTITION BY AOGUID,
                           HOUSEGUID ORDER BY ENDDATE ASC) AS HouseCurrStatus
                        FROM fias_Houses insh  WHERE insh.AOGUID=a_AOGUID AND
                                                insh.HOUSEGUID=a_HOUSEGUID) as ah
				ON h.AOGUID=ah.AOGUID AND h.HOUSEGUID=ah.HOUSEGUID 
                                    AND h.ENDDATE=ah.ENDDATE
        WHERE h.ENDDATE=v_ENDDATE;					
    v_HouseActStatus:=CASE WHEN COALESCE(v_ENDDATE,c_MAXENDDATE)=
                    c_MAXENDDATE THEN c_StatusActual ELSE c_StatusNotActual END;
    v_HouseCurrStatus:=CASE WHEN COALESCE(v_ENDDATE,c_MAXENDDATE)=
                    c_MAXENDDATE THEN 0 ELSE v_HouseCurrStatus END;
    IF v_HOUSENUM IS NOT NULL THEN
        RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
                    c_HouseAOLevel,c_HouseShortTypeName,v_HOUSENUM;
    END IF;
    IF v_BUILDNUM IS NOT NULL THEN
        RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
                                    c_HouseAOLevel,c_BuildShortTypeName,v_BUILDNUM;
    END IF;
    IF v_STRUCNUM IS NOT NULL THEN
        RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
                                 c_HouseAOLevel,c_StructShortTypeName,v_STRUCNUM;
    END IF;
  END;
  $BODY$
 LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE	TIMESTAMP) IS 'Возвращает дерево (список взаимосвязанных строк) с дома характеристиками и его адресного объекта';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE	TIMESTAMP);
/******************************************************************/
/* Возвращает дерево (список взаимосвязанных строк) с характеристиками */
/* дома и других адресообразующих элементов                                           */
/******************************************************************/	
CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree(
    a_HOUSEGUID VARCHAR(36),/* Глобальный уникальный идентификатор дома */
    a_ENDDATE TIMESTAMP default '2079-06-06'/* Окончание действия записи о доме*/
)
RETURNS  TABLE (rtf_GUID  VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER,  rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS
$BODY$
DECLARE
    c_MaxEndDate CONSTANT TIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD');
    c_ActualStatusCode CONSTANT INTEGER :=1;	
                              /* Признак актуальной записи адресного объекта */
    c_NotActualStatusCode CONSTANT INTEGER :=0; 
                              /* Значени кода актуальной записи */
    v_AOGUID VARCHAR(36); /* Глобальный уникальный */
                             /* идентификатор адресного объекта*/
    v_CurrStatus INTEGER; /* Статус актуальности КЛАДР 4: */
                             /* 0 - актуальный, */
                            /*	1-50 - исторический, */
                           /* т.е. объект был переименован, */
                           /* в данной записи приведено */
                           /* одно из прежних его наименований, */
                           /* 51 - переподчиненный*/
    v_Return_Error Integer :=0; /* Код возврата */
--*******************************************************************       
--*******************************************************************
 BEGIN
    SELECT INTO v_AOGUID,v_CurrStatus h.AOGUID,
                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
        FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID 
        WHERE h.HOUSEGUID=a_HOUSEGUID 
            AND h.ENDDATE=COALESCE(a_ENDDATE,c_MaxEndDate)
        ORDER BY h.ENDDATE DESC;
    RETURN QUERY SELECT * FROM fstf_Houses_AddressObjectTree(
                                                        v_AOGUID,a_HOUSEGUID,
                                                        v_CurrStatus,a_ENDDATE);
END;
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE TIMESTAMP) IS 'Возвращает дерево (список взаимосвязанных строк) с дома характеристиками и его адресного объекта';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM fstf_Houses_AddressObjectTree('8b40b028-68eb-4315-a256-ea300c604688','42301ab8-9ead-4f8e-8281-e64f2769a254') ORDER BY rtf_AOLevel;
SELECT * FROM fstf_Houses_AddressObjectTree('42301ab8-9ead-4f8e-8281-e64f2769a254') ORDER BY rtf_AOLevel; 	



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


Комментарии к исходному коду функции можно посмотреть здесь.
код функции
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************/
/* Возвращает строку с адресом дома в соответствии с массивом масок          */
/*****************************************************************/	
CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName(
    a_AOGUID VARCHAR(36), /* Идентификтор адресного объекта */
    a_HOUSEGUID VARCHAR(36), /* Глобальный уникальный идентификатор дома */
    a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}' 
                                  /* Массив масок, управляющий содержанием */
                                   /* строки с адресом дома*/
)
RETURNS VARCHAR(1000) AS
$BODY$
DECLARE
    c_HouseMaskArray	CONSTANT VARCHAR(2)[3]:='{HS,BY,BG}';
                                            /* Массив масок по умолчанию*/
    c_HouseNoMask CONSTANT  VARCHAR(2)[1] :='{HS}';
    c_BodyNoMask CONSTANT  VARCHAR(2)[1] :='{BY}';/* Маска корпуса*/
    c_BuildingNoMask	CONSTANT  VARCHAR(2)[1] :='{BG}';/* Маска строения*/
    c_HouseShortTypeName CONSTANT VARCHAR(10):='д.';
    c_BuildShortTypeName CONSTANT VARCHAR(10):='корп.';
    c_StructShortTypeName CONSTANT VARCHAR(10):='стр.';
    v_ENDDATE TIMESTAMP; /* Окончание действия записи */
    v_HOUSENUM VARCHAR(10);	/* Номер дома */
    v_BUILDNUM VARCHAR(10);	/* Номер корпуса */
    v_STRUCNUM	 VARCHAR(10);	/* Номер строения */
    v_TreeAddressObjectName VARCHAR(1000); 
                                     /* Полное в иерархии название объекта*/ 
    v_Return_Error Integer :=0; /* Код возврата */
--*******************************************************       
--*******************************************************
BEGIN
    v_TreeAddressObjectName:=fsfn_AddressObjects_TreeActualName
                                   (a_AOGUID,a_MaskArray);
    SELECT INTO v_ENDDATE MAX(ENDDATE) 
        FROM fias_Houses 
        WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID;
    SELECT INTO v_HOUSENUM,v_BUILDNUM,v_STRUCNUM HOUSENUM,
                    BUILDNUM,STRUCNUM  
        FROM fias_Houses 
        WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID
                    AND ENDDATE=v_ENDDATE;
    IF  c_HouseNoMask <@ a_MaskArray 
            AND COALESCE(TRIM(v_HOUSENUM),'')<>'' THEN
        v_TreeAddressObjectName:=v_TreeAddressObjectName||
                    CASE WHEN v_TreeAddressObjectName='' THEN '' 
                                ELSE ', ' ||c_HouseShortTypeName||' '||v_HOUSENUM 
                    END;
    END IF;			
    IF  c_BodyNoMask <@ a_MaskArray 
            AND COALESCE(TRIM(v_BUILDNUM),'')<>'' THEN
        v_TreeAddressObjectName:=v_TreeAddressObjectName||
                CASE WHEN v_TreeAddressObjectName='' THEN '' 
                        ELSE ', ' ||	c_BuildShortTypeName||' '||v_BUILDNUM 
                END;
    END IF;							
    IF  c_BuildingNoMask <@ a_MaskArray 
            AND COALESCE(TRIM(v_STRUCNUM),'')<>'' THEN
        v_TreeAddressObjectName:=v_TreeAddressObjectName||
                CASE WHEN v_TreeAddressObjectName='' THEN '' 
                        ELSE ', ' ||	c_StructShortTypeName||' '||v_STRUCNUM 
                 END;
    END IF;							
    RETURN 	v_TreeAddressObjectName;
 END;
$BODY$
LANGUAGE plpgsql ;
COMMENT ON FUNCTION fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID	VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS 'Возвращает строку с адресом дома в соответствии с массивом масок';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************/
/* Возвращает строку с адресом дома в соответствии с массивом масок          */
/*****************************************************************/	
CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName(
    a_HOUSEGUID VARCHAR(36),	 /* Глобальный уникальный идентификатор дома */
    a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}'
                                   /* Массив масок, управляющий содержанием */
                                   /* строки с адресом дома*/
)
RETURNS VARCHAR(1000) AS
$BODY$
DECLARE
    c_MaxEndDate CONSTANT TIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD');
    v_AOGUID VARCHAR(36); /* Идентификтор адресного объекта */
    v_TreeAddressObjectName VARCHAR(1000); /* Полное в иерархии название объекта*/ 
    v_Return_Error Integer :=0; /* Код возврата */
--**********************************************************       
--**********************************************************
BEGIN
    SELECT INTO v_AOGUID h.AOGUID	
        FROM fias_Houses h 
            INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID 
        WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE=c_MaxEndDate
        ORDER BY h.ENDDATE DESC;
    v_TreeAddressObjectName:=fsfn_Houses_TreeActualName
                                        (v_AOGUID,a_HOUSEGUID,a_MaskArray);
    RETURN 	v_TreeAddressObjectName;
END;
$BODY$
LANGUAGE plpgsql ;
COMMENT ON FUNCTION fsfn_Houses_TreeActualName(a_HOUSEGUID	VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS 'Возвращает строку с адресом дома в соответствии с массивом масок';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT fsfn_Houses_TreeActualName('8b40b028-68eb-4315-a256-ea300c604688','42301ab8-9ead-4f8e-8281-e64f2769a254');
SELECT fsfn_Houses_TreeActualName('42301ab8-9ead-4f8e-8281-e64f2769a254');



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


Комментарии к исходному коду функции можно посмотреть здесь.
код функции
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20));
/*****************************************************/
/* Возвращает результат поиска в списке адресообразующих   */ 
/* элементов ФИАС по их названию и типу                               */
/*****************************************************/
CREATE OR REPLACE FUNCTION fstf_Houses_SearchByName(
    a_HouseNum VARCHAR(20), /* Номер дома */
    a_BuildNum VARCHAR(10) default NULL,/* Номер Корпуса */
    a_StrucNum VARCHAR(10) default NULL, /* Номер Строения */
    a_FormalName VARCHAR(150) default NULL, /* Оптимизированное */
                                /* для поиска наименование адресного объекта*/
    a_ShortName VARCHAR(20) default NULL, /* Сокращенное */
                                /* наименование типа адресного объекта */
    a_ParentFormalName VARCHAR(150) default NULL, /* Оптимизированное */
                                /* для поиска наименование адресного объекта*/
    a_ParentShortName VARCHAR(20) default NULL, /* Сокращенное */
                                /* наименование типа адресного объекта */
    a_GrandParentFormalName VARCHAR(150) default NULL,
                                  /* Оптимизированное */
                                 /* для поиска наименование адресного объекта*/
    a_GrandParentShortName VARCHAR(20) default NULL
                                 /* Сокращенное */
                                 /* наименование типа адресного объекта */
)
RETURNS TABLE (rtf_AOGUID VARCHAR(36),rtf_HOUSEGUID VARCHAR(36),rtf_AOLevel INTEGER,rtf_HousesFullName VARCHAR(1000),rtf_HouseNum VARCHAR(20),rtf_BuildNum VARCHAR(10),rtf_StrucNum VARCHAR(10),rtf_EndDate TIMESTAMP,rtf_ShortName VARCHAR(20),rtf_FormalName VARCHAR(150), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER,	rtf_ParentShortName VARCHAR(20),rtf_ParentFormalName VARCHAR(150),rtf_GrandParentShortName VARCHAR(20),rtf_GrandParentFormalName VARCHAR(150))
AS
$BODY$
DECLARE
    c_WildChar CONSTANT VARCHAR(2)='%';
    c_BlankChar CONSTANT VARCHAR(2)=' ';
    v_HouseNumTemplate VARCHAR(150); /* Шаблон для поиска номера дома*/
    v_BuildNumTemplate VARCHAR(150); /* Шаблон для поиска номера корпуса*/
    v_StrucNumTemplate VARCHAR(150); /* Шаблон для поиска номера Строения*/
    v_FormalNameTemplate VARCHAR(150); /* Шаблон для поиска */
                                 /* наименования адресного объекта*/
    v_ShortNameTemplate VARCHAR(20); /* Шаблон для поиска */
                                /* типа адресного объекта */
    v_ParentFormalNameTemplate VARCHAR(150); /* Шаблон для поиска */
                               /* наименования родительского адресного объекта*/
    v_ParentShortNameTemplate VARCHAR(20); /* Шаблон для поиска */
                               /* типа родительского адресного объекта */
    v_GrandParentFormalNameTemplate VARCHAR(150); /* Шаблон для поиска */
                              /* наименования родительского адресного объекта*/
    v_GrandParentShortNameTemplate VARCHAR(20); /* Шаблон для поиска*/
                             /* типа родительского адресного объекта */
--***************************************************************
--***************************************************************
BEGIN
v_ShortNameTemplate:=UPPER(COALESCE(c_WildChar
                     ||REPLACE(TRIM(a_ShortName),c_BlankChar,c_WildChar)
                     ||c_WildChar,c_WildChar));
v_FormalNameTemplate:=UPPER(COALESCE(c_WildChar
                     ||REPLACE(TRIM(a_FormalName),c_BlankChar,c_WildChar)
                     ||c_WildChar,c_WildChar));
v_HouseNumTemplate:=
                     CASE WHEN TRIM(COALESCE(a_HouseNum,''))='' THEN ''
                            ELSE LOWER(c_WildChar
                              ||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar)) 
                       END 
                    ||CASE WHEN TRIM(COALESCE(a_BuildNum,''))='' THEN ''
                             ELSE LOWER(c_WildChar
                               ||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar)) 
                        END 
                     ||	CASE WHEN TRIM(COALESCE(a_StrucNum,''))='' THEN ''
                              ELSE LOWER(c_WildChar
                                ||REPLACE(TRIM(a_StrucNum),c_BlankChar,c_WildChar)) 
                          END;
v_HouseNumTemplate:=v_HouseNumTemplate||c_WildChar;
v_HouseNumTemplate:=CASE WHEN TRIM(COALESCE(a_HouseNum,''))='' THEN ''
                          ELSE LOWER(c_WildChar
                            ||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar)) 
                           END 
                           ||c_WildChar;
v_BuildNumTemplate:=CASE WHEN TRIM(COALESCE(a_BuildNum,''))='' THEN '' 
                            ELSE LOWER(c_WildChar
                              ||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar)) 
                             END ||c_WildChar;
v_StrucNumTemplate:=CASE WHEN TRIM(COALESCE(a_StrucNum,''))='' THEN ''
                              ELSE LOWER(c_WildChar
                                  ||REPLACE(TRIM(a_StrucNum),c_BlankChar,c_WildChar)) 
                              END||c_WildChar;
IF a_FormalName IS NOT NULL
        AND a_ParentFormalName IS NULL AND a_ParentShortName IS NULL
        AND a_GrandParentFormalName IS NULL AND a_GrandParentShortName IS NULL 
THEN
    IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL  
         OR a_StrucNum IS NOT NULL 
    THEN
        RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                       fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                       h.HouseNum,h.BuildNum,h.StrucNum,
                       h.EndDate,cfa.ShortName,cfa.FORMALNAME,
                       cfa.currstatus,cfa.Actstatus,
                       NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR
            FROM fias_AddressObjects cfa
                INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
            WHERE cfa.currstatus=
                   CASE WHEN 0 < 
                        ALL(SELECT iao.currstatus 
                                 FROM fias_AddressObjects iao 
                                 WHERE cfa.aoguid = iao.aoguid)
                          THEN (SELECT MAX(iao.currstatus) 
                                  FROM fias_AddressObjects iao 
                                  WHERE cfa.aoguid = iao.aoguid)
                           ELSE 0 END
                     AND h.EndDate=(SELECT MAX(ih.EndDate) 
                                 FROM fias_Houses ih 
                                 WHERE cfa.aoguid = ih.aoguid 
                                     AND h.HouseGUID = ih.HouseGUID)
                                     AND UPPER(cfa.FORMALNAME) 
                                         LIKE v_FormalNameTemplate 
                                     AND UPPER(cfa.ShortName) 
                                         LIKE v_ShortNameTemplate
                                     AND TRIM(LOWER(COALESCE(h.HouseNum,''))) 
                                         LIKE v_HouseNumTemplate
                                     AND TRIM(LOWER(COALESCE(h.BuildNum,''))) 
                                         LIKE v_BuildNumTemplate
                                     AND TRIM(LOWER(COALESCE(h.StrucNum,''))) 
                                         LIKE v_StrucNumTemplate
            ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME,
                TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),h.HouseNum,
                TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),h.BuildNum,
                TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum;
    ELSE
        RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                    fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                    h.HouseNum,h.BuildNum,h.StrucNum,h.EndDate,
                    cfa.ShortName,cfa.FORMALNAME,cfa.currstatus,cfa.Actstatus,
                    NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR
        FROM fias_AddressObjects cfa
            INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
        WHERE cfa.currstatus=
                CASE WHEN 0 < ALL(SELECT iao.currstatus 
                                FROM fias_AddressObjects iao 
                                WHERE cfa.aoguid = iao.aoguid)
                    THEN (SELECT MAX(iao.currstatus) 
                                FROM fias_AddressObjects iao 
                                WHERE cfa.aoguid = iao.aoguid)
                    ELSE 0 END
                AND h.EndDate=(SELECT MAX(ih.EndDate) 
                                FROM fias_Houses ih 
                                WHERE cfa.aoguid = ih.aoguid 
                                    AND h.HouseGUID = ih.HouseGUID)
                                    AND UPPER(cfa.FORMALNAME) 
                                        LIKE v_FormalNameTemplate 
                                    AND UPPER(cfa.ShortName) 
                                        LIKE v_ShortNameTemplate
        ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME,
            TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),h.HouseNum,
            TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),h.BuildNum,
            TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum;
    END IF;
ELSIF a_FormalName IS NOT NULL AND a_ParentFormalName IS NOT NULL 
		AND a_GrandParentFormalName IS NULL AND a_GrandParentShortName IS NULL 
THEN	    
     v_ParentShortNameTemplate:=UPPER(COALESCE(c_WildChar
                     ||REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)
                     ||c_WildChar,c_WildChar));
    v_ParentFormalNameTemplate:=UPPER(c_WildChar
                     ||REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)
                     ||c_WildChar);
     v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
     IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL  OR a_StrucNum IS NOT NULL
     THEN
          RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                   fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                   h.HouseNum,h.BuildNum,h.StrucNum,h.EndDate,
                   cfa.ShortName,cfa.FORMALNAME,cfa.currstatus,
                   cfa.Actstatus,pfa.ShortName,pfa.FORMALNAME,
                   NULL::VARCHAR,NULL::VARCHAR
          FROM fias_AddressObjects pfa
              INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
              INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
          WHERE cfa.currstatus=
              CASE WHEN 0 < ALL(SELECT iao.currstatus 
                               FROM fias_AddressObjects iao 
                               WHERE cfa.aoguid = iao.aoguid)
                         THEN (SELECT MAX(iao.currstatus) 
                               FROM fias_AddressObjects iao 
                               WHERE cfa.aoguid = iao.aoguid)
                          ELSE 0 END
               AND h.EndDate=(SELECT MAX(ih.EndDate)
                                FROM fias_Houses ih 
                               WHERE cfa.aoguid = ih.aoguid 
                                   AND h.HouseGUID = ih.HouseGUID)
                AND pfa.currstatus=
                        CASE WHEN 0 < ALL(SELECT iao.currstatus 
                               FROM fias_AddressObjects iao 
                               WHERE cfa.aoguid = iao.aoguid)
                         THEN (SELECT MAX(iao.currstatus) 
                               FROM fias_AddressObjects iao 
                               WHERE pfa.aoguid = iao.aoguid)
                           ELSE 0 END
                AND UPPER(pfa.FORMALNAME) 
                    LIKE v_ParentFormalNameTemplate 
                AND UPPER(pfa.ShortName) 
                    LIKE v_ParentShortNameTemplate
                AND UPPER(cfa.FORMALNAME) 
                    LIKE v_FormalNameTemplate 
                AND UPPER(cfa.ShortName) 
                    LIKE v_ShortNameTemplate
                AND TRIM(LOWER(COALESCE(h.HouseNum,''))) 
                    LIKE v_HouseNumTemplate
                AND TRIM(LOWER(COALESCE(h.BuildNum,''))) 
                    LIKE v_BuildNumTemplate
                AND TRIM(LOWER(COALESCE(h.StrucNum,''))) 
                    LIKE v_StrucNumTemplate
          ORDER BY pfa.ShortName,pfa.FORMALNAME,cfa.AOLevel,
                    cfa.ShortName,cfa.FORMALNAME,
                    TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),
                    h.HouseNum,TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),
                    h.BuildNum,TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),
                    h.StrucNum;
     ELSE
          RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                   fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                   h.HouseNum,h.BuildNum,h.StrucNum,
                   h.EndDate,cfa.ShortName,cfa.FORMALNAME,
                   cfa.currstatus,cfa.Actstatus,pfa.ShortName,
                   pfa.FORMALNAME,NULL::VARCHAR,NULL::VARCHAR
         FROM fias_AddressObjects pfa
             INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
             INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
         WHERE cfa.currstatus=
              CASE WHEN 0 < ALL(SELECT iao.currstatus 
                               FROM fias_AddressObjects iao 
                               WHERE cfa.aoguid = iao.aoguid)
                        THEN (SELECT MAX(iao.currstatus) 
                               FROM fias_AddressObjects iao 
                               WHERE cfa.aoguid = iao.aoguid)
                         ELSE 0 END
                   AND h.EndDate=(SELECT MAX(ih.EndDate) 
                              FROM fias_Houses ih 
                              WHERE cfa.aoguid = ih.aoguid 
                                  AND h.HouseGUID = ih.HouseGUID)
                                  AND pfa.currstatus=
                                  CASE WHEN 0 < ALL(SELECT iao.currstatus 
                                        FROM fias_AddressObjects iao 
                                        WHERE cfa.aoguid = iao.aoguid)
                                   THEN (SELECT MAX(iao.currstatus) 
                                        FROM fias_AddressObjects iao 
                                        WHERE pfa.aoguid = iao.aoguid)
                                    ELSE 0 END
                  AND UPPER(pfa.FORMALNAME) 
                      LIKE v_ParentFormalNameTemplate 
                  AND UPPER(pfa.ShortName) 
                      LIKE v_ParentShortNameTemplate
                  AND UPPER(cfa.FORMALNAME) 
                      LIKE v_FormalNameTemplate 
                  AND UPPER(cfa.ShortName) 
                      LIKE v_ShortNameTemplate
         ORDER BY pfa.ShortName,pfa.FORMALNAME,cfa.AOLevel,
                 cfa.ShortName,cfa.FORMALNAME,
                 TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),
                 h.HouseNum,
                 TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),
                 h.BuildNum,
                 TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum;
     END IF;
ELSE 
     v_GrandParentShortNameTemplate:=COALESCE(UPPER(
             COALESCE(c_WildChar
              ||REPLACE(TRIM(a_GrandParentShortName),c_BlankChar,c_WildChar)
              ||c_WildChar,c_WildChar)),c_WildChar);
     v_GrandParentFormalNameTemplate:=COALESCE(UPPER(
               c_WildChar
               ||REPLACE(TRIM(a_GrandParentFormalName),c_BlankChar,c_WildChar)
             ||c_WildChar),c_WildChar);
     v_ParentShortNameTemplate:=COALESCE(UPPER(
                     COALESCE(c_WildChar
                     ||REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)
                     ||c_WildChar,c_WildChar)),c_WildChar);
     v_ParentFormalNameTemplate:=COALESCE(UPPER(
                     c_WildChar||REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)
                     ||c_WildChar),c_WildChar);
     v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
     IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL  
          OR a_StrucNum IS NOT NULL 
     THEN
          RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                   fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                   h.HouseNum,h.BuildNum,h.StrucNum,
                   h.EndDate,cfa.ShortName,cfa.FORMALNAME,
                   cfa.currstatus,cfa.Actstatus,pfa.ShortName,
                   pfa.FORMALNAME,gpfa.ShortName,gpfa.FORMALNAME
          FROM fias_AddressObjects gpfa
              INNER JOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
              INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
              INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
          WHERE cfa.currstatus=
              CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
               THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                ELSE 0 END
               AND pfa.currstatus=
              CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
               THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE pfa.aoguid = iao.aoguid)
                ELSE 0 END
                 AND gpfa.currstatus=
                 CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                  THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE gpfa.aoguid = iao.aoguid)
                   ELSE 0 END
                   AND h.EndDate=(SELECT MAX(ih.EndDate) 
                              FROM fias_Houses ih 
                              WHERE cfa.aoguid = ih.aoguid 
                                  AND h.HouseGUID = ih.HouseGUID)
                                  AND UPPER(gpfa.FORMALNAME) 
                                      LIKE v_GrandParentFormalNameTemplate 
                                  AND UPPER(gpfa.ShortName) 
                                      LIKE v_GrandParentShortNameTemplate
                                  AND UPPER(pfa.FORMALNAME) 
                                      LIKE v_ParentFormalNameTemplate 
                                  AND UPPER(pfa.ShortName) 
                                      LIKE v_ParentShortNameTemplate
                                  AND UPPER(cfa.FORMALNAME) 
                                      LIKE v_FormalNameTemplate 
                                  AND UPPER(cfa.ShortName) 
                                      LIKE v_ShortNameTemplate
                                  AND TRIM(LOWER(COALESCE(h.HouseNum,''))) 
                                      LIKE v_HouseNumTemplate
                                  AND TRIM(LOWER(COALESCE(h.BuildNum,''))) 
                                      LIKE v_BuildNumTemplate
                                  AND TRIM(LOWER(COALESCE(h.StrucNum,''))) 
                                      LIKE v_StrucNumTemplate
          ORDER BY gpfa.ShortName,gpfa.FORMALNAME,pfa.ShortName,
              pfa.FORMALNAME,cfa.AOLevel,cfa.ShortName,
              cfa.FORMALNAME,
              TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),
              h.HouseNum,
              TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),
              h.BuildNum,
              TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum;
     ELSE
          RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel,
                   fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID),
                   h.HouseNum,h.BuildNum,h.StrucNum,
                   h.EndDate,cfa.ShortName,cfa.FORMALNAME,
                   cfa.currstatus,cfa.Actstatus,pfa.ShortName,
                   pfa.FORMALNAME,gpfa.ShortName,gpfa.FORMALNAME
          FROM fias_AddressObjects gpfa
              INNER JOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
              INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
              INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid
          WHERE cfa.currstatus=
              CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                  ELSE 0 END
              AND pfa.currstatus=
               CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                 THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE pfa.aoguid = iao.aoguid)
                  ELSE 0 END
              AND gpfa.currstatus=
               CASE WHEN 0 < ALL(SELECT iao.currstatus 
                              FROM fias_AddressObjects iao 
                              WHERE cfa.aoguid = iao.aoguid)
                 THEN (SELECT MAX(iao.currstatus) 
                              FROM fias_AddressObjects iao 
                              WHERE gpfa.aoguid = iao.aoguid)
                 ELSE 0 END
              AND h.EndDate=(SELECT MAX(ih.EndDate) 
                              FROM fias_Houses ih 
                              WHERE cfa.aoguid = ih.aoguid 
                                  AND h.HouseGUID = ih.HouseGUID)
                                  AND UPPER(gpfa.FORMALNAME) 
                                      LIKE v_GrandParentFormalNameTemplate 
                                  AND UPPER(gpfa.ShortName) 
                                      LIKE v_GrandParentShortNameTemplate
                                  AND UPPER(pfa.FORMALNAME) 
                                      LIKE v_ParentFormalNameTemplate 
                                  AND UPPER(pfa.ShortName) 
                                      LIKE v_ParentShortNameTemplate
                                  AND UPPER(cfa.FORMALNAME) 
                                      LIKE v_FormalNameTemplate 
                                  AND UPPER(cfa.ShortName) 
                                      LIKE v_ShortNameTemplate
          ORDER BY gpfa.ShortName,gpfa.FORMALNAME,pfa.ShortName,
                  pfa.FORMALNAME,cfa.AOLevel,cfa.ShortName,
                  cfa.FORMALNAME,
                  TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),
                  h.HouseNum,
                  TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),
                  h.BuildNum,
                  TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),
                  h.StrucNum;
     END IF;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20))
IS 'Возвращает результат поиска в списке адресообразующих элементов ФИАС по их названию и типу';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
--SELECT * FROM fstf_Houses_SearchByName('220');
--SELECT * FROM fstf_Houses_SearchByName(NULL,NULL,'220');
SELECT * FROM fstf_Houses_SearchByName('1',NULL,NULL,'Красноярск');
SELECT * FROM fstf_Houses_SearchByName(NULL,NULL,NULL,'Юбилейная','УЛ','Абан',NULL,'Абанский');


Создание таблицы домов ФИАС fias_Houses


код скрипта
BEGIN TRANSACTION;
DROP TABLE IF EXISTS fias_Houses;
DROP TABLE IF EXISTS fias_EstateStatus;
DROP TABLE IF EXISTS fias_StructureStatus;
CREATE TABLE IF NOT EXISTS fias_Houses(
	HOUSEID		VARCHAR(36) NOT NULL,
	AOGUID		VARCHAR(36) NULL,
	HOUSEGUID		VARCHAR(36) NULL,
	HOUSENUM		VARCHAR(10) NULL,
	BUILDNUM		VARCHAR(10) NULL,
	STRUCNUM		VARCHAR(10) NULL,
	POSTALCODE	VARCHAR(6) NULL,
	OKATO			VARCHAR(11) NULL,
	OKTMO			VARCHAR(11) NULL,
	IFNSFL		VARCHAR(4) NULL,
	TERRIFNSFL	VARCHAR(4) NULL,
	IFNSUL		VARCHAR(4) NULL,
	TERRIFNSUL	VARCHAR(4) NULL,
	ESTSTATUS		INTEGER NULL,
	STATSTATUS	INTEGER NULL,
	STRSTATUS		INTEGER NULL,
	STARTDATE		TIMESTAMP NULL,
	ENDDATE		TIMESTAMP NULL,
	UPDATEDATE	TIMESTAMP NULL,
	NORMDOC		VARCHAR(36) NULL,
	COUNTER		INTEGER NULL,
	CADNUM		VARCHAR(50) NULL, 
	DIVTYPE		INTEGER NULL,
CONSTRAINT XPKfias_Houses PRIMARY KEY  
(
	HOUSEID
)) WITH (OIDS=False);

CREATE  INDEX XIE1fias_Houses ON fias_Houses(AOGUID);
CREATE  INDEX XIE2fias_Houses ON fias_Houses(HOUSEGUID);
CREATE  INDEX XIE3fias_Houses ON fias_Houses(AOGUID,HOUSEGUID);
CREATE  INDEX XIE4fias_Houses ON fias_Houses(HOUSENUM,BUILDNUM,STRUCNUM);
CREATE  INDEX XIE5fias_Houses ON fias_Houses(HOUSENUM);
CREATE  INDEX XIE6fias_Houses ON fias_Houses(BUILDNUM);
CREATE  INDEX XIE7fias_Houses ON fias_Houses(STRUCNUM);

COMMENT ON TABLE fias_Houses IS 'HOUSE  Сведения по номерам домов улиц городов и населенных пунктов, номера земельных участков и т.п';
COMMENT ON COLUMN fias_Houses.HOUSEID IS 'Уникальный идентификатор записи дома';
COMMENT ON COLUMN fias_Houses.AOGUID IS 'Глобальный уникальный идентификатор записи родительского объекта (улицы, города, населенного пункта и т.п.)';
COMMENT ON COLUMN fias_Houses.HOUSEGUID IS 'Глобальный уникальный идентификатор дома';
COMMENT ON COLUMN fias_Houses.HOUSENUM IS 'Номер дома';
COMMENT ON COLUMN fias_Houses.BUILDNUM IS 'Номер корпуса';
COMMENT ON COLUMN fias_Houses.STRUCNUM IS 'Номер строения';

COMMENT ON COLUMN fias_Houses.POSTALCODE IS 'Почтовый индекс';
COMMENT ON COLUMN fias_Houses.IFNSFL IS 'Код ИФНС ФЛ';
COMMENT ON COLUMN fias_Houses.TERRIFNSFL IS 'Код территориального участка ИФНС ФЛ';
COMMENT ON COLUMN fias_Houses.IFNSUL IS 'Код ИФНС ЮЛ';
COMMENT ON COLUMN fias_Houses.TERRIFNSUL IS 'Код территориального участка ИФНС ЮЛ';
COMMENT ON COLUMN fias_Houses.OKATO IS 'ОКАТО';
COMMENT ON COLUMN fias_Houses.OKTMO IS 'ОКТМО';
COMMENT ON COLUMN fias_Houses.ESTSTATUS IS 'Признак владения';
COMMENT ON COLUMN fias_Houses.STRSTATUS IS 'Признак строения';
COMMENT ON COLUMN fias_Houses.STATSTATUS IS 'Состояние дома';
COMMENT ON COLUMN fias_Houses.STARTDATE IS 'Начало действия записи';
COMMENT ON COLUMN fias_Houses.ENDDATE IS 'Окончание действия записи';
COMMENT ON COLUMN fias_Houses.UPDATEDATE IS 'Дата  внесения (обновления) записи';
COMMENT ON COLUMN fias_Houses.NORMDOC IS 'Внешний ключ на нормативный документ';
COMMENT ON COLUMN fias_Houses.COUNTER IS 'Счетчик записей домов для КЛАДР 4';
COMMENT ON COLUMN fias_Houses.CADNUM IS 'Кадастровый номер здания';
COMMENT ON COLUMN fias_Houses.DIVTYPE IS 'Тип деления: 0 – не определено 1 – муниципальное 2 – административное';

CREATE TABLE IF NOT EXISTS fias_EstateStatus(

	EstateStatusID	INTEGER NOT NULL,
	EstateStatusName varchar(60) NULL,
	EstateStatusShortName varchar(20) NULL,
CONSTRAINT XPKfias_EstateStatus PRIMARY KEY (EstateStatusID)) WITH (OIDS=False);

COMMENT ON TABLE  fias_EstateStatus IS 'Справочник (перечень) видов владений';

COMMENT ON COLUMN fias_EstateStatus.EstateStatusID  IS 'Признак владения. Принимает значение:0 – Не определено,1 – Владение,2 – Дом,3 – Домовладение';
COMMENT ON COLUMN fias_EstateStatus.EstateStatusName  IS 'Наименование';
COMMENT ON COLUMN fias_EstateStatus.EstateStatusShortName  IS 'Краткое наименование';

CREATE TABLE IF NOT EXISTS fias_StructureStatus(

	StructureStatusID	INTEGER NOT NULL,
	StructureStatusName varchar(60) NULL,
	StructureStatusShortName varchar(20) NULL,
CONSTRAINT XPKfias_StructureStatus PRIMARY KEY (StructureStatusID)) WITH (OIDS=False);

COMMENT ON TABLE  fias_StructureStatus IS 'Справочник (перечень) видов строений';

COMMENT ON COLUMN fias_StructureStatus.StructureStatusID  IS 'Признак строения. Принимает значение:0 – Не определено,1 – Строение,2 – Сооружение,3 – Литер';
COMMENT ON COLUMN fias_StructureStatus.StructureStatusName  IS 'Наименование';
COMMENT ON COLUMN fias_StructureStatus.StructureStatusShortName  IS 'Краткое наименование';
--ROLLBACk TRANSACTION;
COMMIT TRANSACTION;



Загрузка обновлений домов ФИАС в таблицу fias_Houses


исходный код скрипта
BEGIN TRANSACTION;
do $$
BEGIN
/****************************************/
/* Создание временных таблиц                          */
/****************************************/
DROP TABLE IF EXISTS fias_DeletedHouses_temp;
DROP TABLE IF EXISTS fias_Houses_temp;
DROP TABLE IF EXISTS fias_EstateStatus_temp;
DROP TABLE IF EXISTS fias_StructureStatus_temp;

CREATE TABLE fias_Houses_temp AS 
  SELECT * FROM fias_Houses LIMIT 1;
DELETE FROM fias_Houses_temp;  
CREATE TABLE fias_DeletedHouses_temp AS 
  SELECT * FROM fias_Houses LIMIT 1;
DELETE FROM fias_DeletedHouses_temp; 
CREATE TABLE fias_EstateStatus_temp AS 
  SELECT * FROM fias_EstateStatus LIMIT 1;
 DELETE FROM fias_EstateStatus_temp; 
CREATE TABLE fias_StructureStatus_temp AS 
  SELECT * FROM fias_StructureStatus LIMIT 1;
 DELETE FROM fias_StructureStatus_temp; 
 /*****************************************************/
/* Загрузка во временную таблицу fias_EstateStatus изменений */
/* справочника  "Признак владения" домов  ФИАС                    */
/*****************************************************/
COPY  fias_EstateStatus_temp(EstateStatusID,EstateStatusNAME,EstateStatusShortName) 
FROM 'W:\Projects\Enisey GIS\DB\SourceData\ESTSTAT_20180827.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');
/*************************************************/
/* Обновление существующих записей справочника          */
/* "Признак владения" домов  ФИАС данными обновления */
/*************************************************/

UPDATE fias_EstateStatus s SET EstateStatusNAME=t.EstateStatusNAME,
				EstateStatusShortName=t.EstateStatusShortName
			FROM fias_EstateStatus ds
				INNER JOIN fias_EstateStatus_temp t ON ds.EstateStatusID=t.EstateStatusID
			WHERE ds.EstateStatusID=s.EstateStatusID;	

INSERT INTO fias_EstateStatus(EstateStatusID,EstateStatusNAME,EstateStatusShortName) 
	SELECT EstateStatusID,EstateStatusNAME,EstateStatusShortName FROM fias_EstateStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_EstateStatus os WHERE t.EstateStatusID=os.EstateStatusID);
/******************************************************/
/* Загрузка во временную таблицу fias_StructureStatus изменений  */
/* справочника  "Видов строений" ФИАС                                    */
/******************************************************/
COPY  fias_StructureStatus_temp(StructureStatusID,StructureStatusNAME,StructureStatusShortName) 
FROM 'W:\Projects\Enisey GIS\DB\SourceData\STRSTAT_20180827.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');
/*****************************************************************/
/* Обновление существующих записей справочника "Видов строений" ФИАС   */
/* данными обновления                                                                                  */
/*****************************************************************/
UPDATE fias_StructureStatus s SET StructureStatusNAME=t.StructureStatusNAME,
				StructureStatusShortName=t.StructureStatusShortName
			FROM fias_StructureStatus ds
				INNER JOIN fias_StructureStatus_temp t ON ds.StructureStatusID=t.StructureStatusID
			WHERE ds.StructureStatusID=s.StructureStatusID;	

INSERT INTO fias_StructureStatus(StructureStatusID,StructureStatusNAME,StructureStatusShortName) 
	SELECT StructureStatusID,StructureStatusNAME,StructureStatusShortName FROM fias_StructureStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_StructureStatus os WHERE t.StructureStatusID=os.StructureStatusID);

/***********************************************************************/
/* Загрузка во временную таблицу fias_Houses_temp записей изменений домов ФИАС */
/**********************************************************************/
COPY  fias_Houses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) 
FROM 'W:\Projects\Enisey GIS\DB\SourceData\HOUSE24_20180827.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');

/************************************************************/
/* Загрузка во временную таблицу fias_DeletedHouses_temp записей,    */
/* которые должны быть удалены из основнго списка                            */
/************************************************************/
/* Файл DHOUSE24 в кючается в обносление ФИАС редко.                     */
/* Поэтому его загрузка здесь приведена только как комментарий         */ 
/************************************************************/
/*
COPY  fias_DeletedHouses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) 
FROM 'W:\Projects\Enisey GIS\DB\SourceData\DHOUSE24_20180827.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');
*/
/***********************************************************************/
/* Обновление существующих записей списка домов ФИАС данными обновления*/
/***********************************************************************/
UPDATE 	fias_Houses h SET AOGUID=t.AOGUID,
			BUILDNUM=t.BUILDNUM,
			ENDDATE=t.ENDDATE,
			ESTSTATUS=t.ESTSTATUS,
			HOUSEGUID=t.HOUSEGUID,
			HOUSENUM=t.HOUSENUM,
			STATSTATUS=t.STATSTATUS,
			IFNSFL=t.IFNSFL,
			IFNSUL=t.IFNSUL,
			OKATO=t.OKATO,
			OKTMO=t.OKTMO,
			POSTALCODE=t.POSTALCODE,
			STARTDATE=t.STARTDATE,
			STRUCNUM=t.STRUCNUM,
			STRSTATUS=t.STRSTATUS,
			TERRIFNSFL=t.TERRIFNSFL,
			TERRIFNSUL=t.TERRIFNSUL,
			UPDATEDATE=t.UPDATEDATE,
			NORMDOC=t.NORMDOC,
			COUNTER=t.COUNTER,
			CADNUM=t.CADNUM, 
			DIVTYPE=t.DIVTYPE
		FROM fias_Houses dh
			INNER JOIN fias_Houses_Temp t ON t.HOUSEID=dh.HOUSEID
		WHERE h.HOUSEID=dh.HOUSEID;	

/****************************************************/
/* Удаление существующих записей списка домов ФИАС         */
/* на основании данных из временной таблицы                      */
/* fias_DeletedHouses_temp                                                    */
/****************************************************/
DELETE FROM fias_Houses h WHERE EXISTS(SELECT 1 FROM fias_DeletedHouses_temp delh WHERE delh.HOUSEID=h.HOUSEID);
/****************************************************/
/* Добавление вновь поступивших записей основного списка */
/* домов fias_Houses, данными                                               */
/* из временной таблицы fias_Houses_Temp                            */
/****************************************************/

INSERT INTO fias_Houses(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) 
SELECT AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE 	
		FROM fias_Houses_Temp t WHERE NOT EXISTS(SELECT * FROM  fias_Houses h WHERE t.HOUSEID=h.HOUSEID);

/************************************/
/* Удаление временных таблиц                  */
/************************************/
DROP TABLE IF EXISTS fias_DeletedHouses_temp;
DROP TABLE IF EXISTS fias_Houses_temp;
DROP TABLE IF EXISTS fias_EstateStatus_temp;
DROP TABLE IF EXISTS fias_StructureStatus_temp;
END;
$$LANGUAGE plpgsql;
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT (SELECT COUNT(*) FROM fias_Houses) AS HouseCount,
(SELECT COUNT(*) FROM fias_EstateStatus) AS EStatusCount,
(SELECT COUNT(*) FROM fias_StructureStatus) AS SStatusCount;

Tags:ФИАСPostgreSQLstored procedures
Hubs: PostgreSQL
+12
11.6k 66
Comments 4