PostgreSQL में FIAS होम्स

 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; 

 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; 

 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; 

 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; ; 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; 

 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'); 

 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,''); 

 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; 

 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;