BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER);
/****************************************************************************************************************/
/* Возвращает дерево (список взаимосвязанных строк) с характеристиками адресного объекта			*/
/*--------------------------------------------------------------------------------------------------------------*/
/* Например:													*/
/* Результат вызова функции (SELECT * FROM fstf_AddressObjects_AddressObjectTree('bfc1236d-b5d2-4734-a238-3b1e4830e963');)*/
/*----------------------------------------------------------------------------------------------------------------------*/
/* AOGUID				CurrStatus 	ActStatus 	AOLevel		ShortName	FormalName	*/
/*----------------------------------------------------------------------------------------------------------------------*/
/*db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1		0		1		1	край		Красноярский	*/
/*625497d3-22de-4390-b4b4-2febfbfc15ce		0		1		3	р-н		Балахтинский	*/
/*39da6405-b3e6-4baf-b332-d47b73b4d5fb		0		1		6	п		Могучий		*/
/*bfc1236d-b5d2-4734-a238-3b1e4830e963		0		1		7	ул		Новая		*/
/*----------------------------------------------------------------------------------------------------------------------*/
/****************************************************************************************************************/	
CREATE OR REPLACE FUNCTION fstf_AddressObjects_AddressObjectTree(
		a_AOGUID	VARCHAR(36),			/* Глобальный уникальный идентификатор адресного объекта*/
		a_CurrStatus	INTEGER default NULL		/* Статус актуальности КЛАДР 4:			*/
								/*	0 - актуальный, 			*/
								/*	1-50 - исторический, т.е. объект был переименован, в данной записи приведено одно из прежних его наименований, 	*/
								/*	51 - переподчиненный 			 */
						
)
RETURNS  TABLE (rtf_AOGUID  VARCHAR(36), rtf_CurrStatus INTEGER, rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS
$BODY$
DECLARE
	v_Return_Error		Integer :=0;	/* Код возврата */
--******************************************************************************************************       
--******************************************************************************************************
 BEGIN
	IF a_CurrStatus IS NOT NULL THEN
		RETURN QUERY WITH RECURSIVE child_to_parents AS (
						  SELECT ao.* FROM fias_AddressObjects ao
							WHERE aoguid=a_AOGUID AND ao.currstatus = a_CurrStatus
						  UNION ALL
						  SELECT ao.* FROM fias_AddressObjects ao, child_to_parents
						      WHERE ao.aoguid = child_to_parents.parentguid
							AND ao.currstatus = CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
											THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
											ELSE 0 END
						)
			SELECT AOGUID,CurrStatus,ActStatus,AOLevel, ShortName,FormalName FROM child_to_parents ORDER BY aolevel;
	ELSE
		RETURN QUERY WITH RECURSIVE child_to_parents AS (
							  SELECT ao.* FROM fias_AddressObjects ao
							      WHERE aoguid=a_AOGUID 
								AND ao.currstatus = CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
									THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
								ELSE 0 END
							  UNION ALL
							  SELECT ao.* FROM fias_AddressObjects ao, child_to_parents
							      WHERE ao.aoguid = child_to_parents.parentguid
								AND ao.currstatus = CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
												THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)
												ELSE 0 END
							)
						SELECT AOGUID,CurrStatus,ActStatus,AOLevel, ShortName,FormalName FROM child_to_parents ORDER BY aolevel;
	END IF;	
END;
  $BODY$
 LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), a_CurrStatus INTEGER)
					IS 'Возвращает дерево (список взаимосвязанных строк) с характеристиками адресного объекта';


--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECT * FROM fstf_AddressObjects_AddressObjectTree('bfc1236d-b5d2-4734-a238-3b1e4830e963',0) ORDER BY rtf_AOLevel;

