CREATE OR REPLACE FUNCTION SP_EHR_RUS_ChangeRefs(
	_Entity TEXT,
	_Attr TEXT,
	_EhrReplacement uuid)
	RETURNS TEXT
AS $$
DECLARE 
	_tsql text;
	_txt text;
BEGIN

_tsql = 'update entity 
set ' || _Attr || ' = (
SELECT TOP 1 case when eer.NewEhr is not null then eer.NewEhr else entity.'|| _Attr ||' end)
from ' || _Entity ||' entity left join EHR.T_ELEMENT_EHR_REPLACEMENT eer
on eer.OldEhr = entity.' || _Attr ||'
where eer.EhrReplacement = ''' || CAST(@EhrReplacement as nvarchar(40)) || '''
UPDATE EHR.T_EHR_REFERENCE
SET EhrRusRefBooks = (
SELECT er.EhrRusRefBooks FROM EHR.T_EHR_RUS er
WHERE EhrRusID = (
SELECT NewEhr FROM EHR.T_ELEMENT_EHR_REPLACEMENT eer 
WHERE eer.EhrReplacement = ''' || _EhrReplacement::text || ''' limit 1))
WHERE Entities = (
SELECT e.EntityID FROM VCLib.T_ENTITIES e WHERE e."schema" || ''.T_'' || e.EntName = ''' || _Entity || ''')
AND AttributeName = ''' || _Attr || ''''
;
_txt := 'begin(p)
  @p := new Process("ChangeServerHolderState");
  @p->{Parameters}["HolderName"] := "EHR.EhrReferenceHolder";
  @p->{Parameters}["State"] := "Refresh";
  @p->Run()[@p];
end;';

EXECUTE _tsql;
select _txt as lexem;
return _txt as lexem;

end;
$$ Language plpgsql;
