DROP PROCEDURE IF EXISTS DBO.sp_updateclassificationgroupsrds;

DROP PROCEDURE IF EXISTS sp_updateclassificationgroupsrds;

CREATE OR REPLACE PROCEDURE 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;

END;
$$;