CREATE OR REPLACE FUNCTION SP_EHR_RUS_ChangeRefs
(
	"Entity" varchar(100)
,	"Attr" varchar(100)
,	"EhrReplacement" uuid
)
RETURNS text
LANGUAGE 'plpgsql'
VOLATILE 
AS $BODY$
declare _tsql text;
Begin

DECLARE "EhrRusRefBooks" VARCHAR(40) := (SELECT NewEhrRefBook FROM EHR.T_EHR_REPLACEMENT er WHERE eer.EhrReplacementID = "EhrReplacement"::varchar(40) limit 1);
_tsql := 'update ' || "Entity" || ' entity
set ' || "Attr" || ' = (
SELECT case when eer.NewEhr is not null then eer.NewEhr else entity.' || "Attr" || ' end limit 1)
from EHR.T_ELEMENT_EHR_REPLACEMENT eer
where eer.OldEhr = entity.' || "Attr" || '
and eer.EhrReplacement = ''' || "EhrReplacement"::varchar(40) || N''';
UPDATE EHR.T_EHR_REFERENCE
SET EhrRusRefBooks = ''' || "EhrRusRefBooks" || '''
WHERE Entities = (SELECT e.EntityID FROM VCLib.T_ENTITIES e WHERE e.Schema || ''.T_'' || e.EntName = ''' || "Entity" || ''')
AND AttributeName = ''' || "Attr" || '''';

--RAISE NOTICE '%', _tsql;
EXECUTE _tsql;		

return 'begin(p)
  @p := new Process("ChangeServerHolderState");
  @p->{Parameters}["HolderName"] := "EHR.EhrReferenceHolder";
  @p->{Parameters}["State"] := "Refresh";
  @p->Run()[@p];
end;';

End;
$BODY$;
ALTER FUNCTION SP_EHR_RUS_ChangeRefs(varchar(100), varchar(100), uuid) OWNER TO postgres;

