DROP FUNCTION IF EXISTS public.sp_f007departmentload;
DROP PROCEDURE IF EXISTS public.sp_f007departmentload;

CREATE PROCEDURE public.sp_f007departmentload (
	"Xml" bytea,
	"FileName" character varying
)
LANGUAGE plpgsql
AS $BODY$
DECLARE
	--меняем кодировку исходного файла на кодировку базы данных, убираем указание на старую кодировку
	xml_doc XML = REPLACE(convert_from("Xml", 'WIN1251'), 'encoding="windows-1251"', '') :: XML;
BEGIN
	DROP TABLE IF EXISTS temp_f007;

	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_f007 AS 
	SELECT
		idved,
		vedname,
		CASE WHEN datebeg = '' THEN NULL ELSE datebeg :: TIMESTAMP END,
		CASE WHEN dateend = '' THEN NULL ELSE dateend :: TIMESTAMP END
	FROM
		XMLTABLE ('/packet/zap' PASSING xml_doc COLUMNS
			idved INT path './IDVED',
			vedname TEXT path './VEDNAME',
			datebeg TEXT path './DATEBEG',
			dateend TEXT path './DATEEND'
		)
	;
	
	-- обновляем существующие записи
	UPDATE ies.t_f007_department AS f007
	SET
		vedname = tmp.vedname,
		dateend = tmp.dateend
	FROM
		temp_f007 AS tmp
	WHERE
		tmp.idved = f007.idved
		AND tmp.datebeg = f007.datebeg
	;

	-- вставляем новые записи, если они есть
	INSERT INTO ies.t_f007_department (
		f007departmentid,
		idved,
		vedname,
		datebeg,
		dateend
	)
	SELECT
		gen_random_uuid(),
		tmp.idved,
		tmp.vedname,
		tmp.datebeg,
		tmp.dateend
	FROM
		temp_f007 AS tmp
	LEFT JOIN
		ies.t_f007_department AS f007
		ON tmp.idved = f007.idved
		AND tmp.datebeg = f007.datebeg
	WHERE
		f007.idved IS NULL
	;

	DROP TABLE IF EXISTS temp_f007;
END;
$BODY$;