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

CREATE PROCEDURE public.sp_f010subjectload (
	"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_f010;

	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_f010 AS 
	SELECT
		kod_tf,
		kod_okato,
		okrug,
		subname,
		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
			kod_tf TEXT PATH './KOD_TF',
			kod_okato TEXT PATH './KOD_OKATO',
			okrug INT PATH './OKRUG',
			subname TEXT PATH './SUBNAME',
			datebeg TEXT PATH './DATEBEG',
			dateend TEXT PATH './DATEEND'
		)
	;
	
	-- обновляем существующие записи
	UPDATE ies.t_f010_subject AS f010
	SET
		subname = tmp.subname,
		dateend = tmp.dateend
	FROM
		temp_f010 AS tmp
	WHERE
		tmp.kod_tf = f010.kod_tf
		AND tmp.datebeg = f010.datebeg
	;

	-- вставляем новые записи, если они есть
	INSERT INTO ies.t_f010_subject (
		subjectid,
		kod_tf,
		kod_okato,
		subname,
		okrug,
		datebeg,
		dateend
	)
	SELECT
		ROW_NUMBER() OVER (ORDER BY tmp.KOD_TF) + (
			SELECT subjectid 
			FROM ies.t_f010_subject
			ORDER BY subjectid DESC
			LIMIT 1
		),
		tmp.kod_tf,
		tmp.kod_okato,
		tmp.subname,
		tmp.okrug,
		tmp.datebeg,
		tmp.dateend
	FROM
		temp_f010 AS tmp
	LEFT JOIN
		ies.t_f010_subject AS f010
		ON tmp.kod_tf = f010.kod_tf
		AND tmp.datebeg = f010.datebeg
	WHERE
		f010.subjectid IS NULL
	;

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