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

CREATE PROCEDURE public.sp_v021medspecload(
	"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_v021;

	-- парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_v021 AS
	SELECT 
		idspec, 
		specname,
		postname,
		CASE WHEN  idpost_mz = '' THEN NULL ELSE idpost_mz::int END ,
		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 
			idspec INT path './IDSPEC', 
			specname TEXT path './SPECNAME',
			postname TEXT path './POSTNAME',
			idpost_mz TEXT path './IDPOST_MZ',
			datebeg TEXT path './DATEBEG', 
			dateend TEXT path './DATEEND'
		)
	;

	-- обновляем существующие записи
	UPDATE ies.t_v021_med_spec AS v021
	SET 
		specname = tmp.specname,
		dateend = tmp.dateend,
		namepost = tmp.postname,
		codepost = tmp.idpost_mz
	FROM temp_v021 AS tmp
	WHERE
		tmp.idspec = v021.code_spec
		AND tmp.datebeg = v021.datebeg
	;

	-- вставляем новые записи, если они есть
	INSERT INTO ies.t_v021_med_spec (
		idspec,
		code_spec,
		specname,
		datebeg,
		dateend,
		namepost,
		codepost
	)
	SELECT
		ROW_NUMBER() OVER (ORDER BY tmp.idspec) + (
			SELECT idspec
			FROM ies.t_v021_med_spec
			ORDER BY idspec DESC
			LIMIT 1
		),
		tmp.idspec,
		tmp.specname,
		tmp.datebeg,
		tmp.dateend,
		tmp.postname,
		tmp.idpost_mz
	FROM
		temp_v021 AS tmp
	LEFT JOIN
		ies.t_v021_med_spec AS v021
		ON tmp.idspec = v021.code_spec
		AND tmp.datebeg = v021.datebeg
	WHERE
		v021.idspec IS NULL
	;
	
	DROP TABLE IF EXISTS temp_v021;		
END;
$BODY$;