DROP PROCEDURE IF EXISTS DBO.sp_updateclassificationgroupsrds;

DROP PROCEDURE IF EXISTS APS.sp_updateclassificationgroupsrds;

CREATE OR REPLACE PROCEDURE APS.sp_updateclassificationgroupsrds
(
	"Xml" text DEFAULT null
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
    language plpgsql
as
$$
Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		-- Получаем данные из XML
		Drop Table If Exists Tmp_classificationgroups;
		Create Temp Table Tmp_classificationgroups
		as
		SELECT	(xpath('/GROUP_OF_DRUGS_CLASSIFICATION/Name[1]/text()', x))[1]::varchar(255) as Name
		,	(xpath('/GROUP_OF_DRUGS_CLASSIFICATION/Code[1]/text()', x))[1]::text::int as Code
		from unnest(xpath('/GROUPS_OF_DRUGS_CLASSIFICATION/body/GROUPS_OF_DRUGS_CLASSIFICATION/GROUP_OF_DRUGS_CLASSIFICATION', "Xml"::xml)) as x;
		
		-- Загружаем новые лекарственные формы
		INSERT INTO pharm.T_GROUPS_OF_DRUGS_CLASSIFICATION(Code, Name)
		SELECT nmf.Code, nmf.Name
		from Tmp_classificationgroups nmf
		where not exists(select* from pharm.T_GROUPS_OF_DRUGS_CLASSIFICATION mf where mf.Code = nmf.Code);
		
		-- Обновляем имеющиеся лекарственные формы
		update pharm.T_GROUPS_OF_DRUGS_CLASSIFICATION mf
		set Name = nmf.Name
		from Tmp_classificationgroups nmf 
		where nmf.Code = mf.Code;

		-- Drop Table
		DROP TABLE if exists Tmp_classificationgroups;
End;
$$;