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

CREATE PROCEDURE public.sp_f011doctypeload (
	"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_f011;

	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_f011 AS 
	SELECT
		iddoc,
		docname,
		docser,
		docnum,
		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
			iddoc INT PATH './IDDoc',
			docname TEXT PATH './DocName',
			docser TEXT PATH './DocSer',
			docnum TEXT PATH './DocNum',
			datebeg TEXT PATH './DATEBEG',
			dateend TEXT PATH './DATEEND'
		)
	;
	
	-- обновляем существующие записи
	UPDATE ies.t_f011_document_type AS f011
	SET
		docname = tmp.docname,
		docser = tmp.docser,
		docnum = tmp.docnum,
		dateend = tmp.dateend
	FROM
		temp_f011 AS tmp
	WHERE
		tmp.iddoc = f011.iddoc
	;

	-- вставляем новые записи, если они есть
	INSERT INTO ies.t_f011_document_type (
		iddoc,
		docname,
		docser,
		docnum,
		datebeg,
		dateend
	)
	SELECT
		tmp.iddoc,
		tmp.docname,
		tmp.docser,
		tmp.docnum,
		tmp.datebeg,
		tmp.dateend
	FROM
		temp_f011 AS tmp
	LEFT JOIN
		ies.t_f011_document_type AS f011
		ON tmp.iddoc = f011.iddoc
	WHERE
		f011.iddoc IS NULL
	;

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