ALTER TABLE ies.t_q017_test_k
    ALTER COLUMN "comment" TYPE varchar(1000);

DROP PROCEDURE IF EXISTS sp_q017testkload;
DROP FUNCTION IF EXISTS sp_q017testkload;

CREATE PROCEDURE public.sp_q017testkload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
declare T xml;
declare tempStrXml text;
BEGIN
	select convert_from("Xml", 'WIN1251') into tempStrXml;
	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_q017;
	CREATE TEMP TABLE temp_q017 as
	SELECT 
		ID_KTEST, 
		NAM_KTEST,
		COMMENT, 
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END , 
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
		ID_KTEST text path './ID_KTEST' null, 
		NAM_KTEST text path './NAM_KTEST' null,
		COMMENT text path './COMMENT' null,
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );
	
	Update IES.T_Q017_TEST_K t2  
		set 
		 ID_KTEST = t1.ID_KTEST
		,NAM_KTEST = t1.NAM_KTEST
		,COMMENT = t1.COMMENT
		,DATEEND = t1.DATEEND
	from temp_q017 t1
	where (t1.ID_KTEST = t2.ID_KTEST and t1.DATEBEG = t2.DATEBEG) and (t2.DATEEND = t1.DATEEND or t2.DATEEND is null );
				
	Insert into ies.T_Q017_TEST_K(COMMENT ,DATEBEG ,DATEEND ,ID_KTEST ,NAM_KTEST ,Q017TestKID)
	Select t1.COMMENT ,t1.DATEBEG ,t1.DATEEND ,t1.ID_KTEST ,t1.NAM_KTEST ,gen_random_uuid()
	from temp_q017 t1
	left join IES.T_Q017_TEST_K t2 on (t1.ID_KTEST = t2.ID_KTEST and t1.DATEBEG = t2.DATEBEG) and (t2.DATEEND = t1.DATEEND or (t2.DATEEND is null and t1.DATEEND is null) )
	where t2.Q017TestKID is null;

	drop table if exists temp_q017;
	
	return;
END
$BODY$;
