DROP FUNCTION IF EXISTS akuz.update_calcaddress CASCADE;

CREATE FUNCTION akuz.update_calcaddress() 
RETURNS TRIGGER 
AS $update_calcaddress$
begin
	
	NEW.CalcAddress = CASE WHEN PA.City is null and PA.Street is null then PA.FreeAddressString
							ELSE
							CONCAT(COALESCE(C.ZIPCode + ' ', ''),
									CASE WHEN RE.RegionType IS NOT NULL AND RE.RegionType != '' THEN RE.RegionType ELSE '' END,
									CASE WHEN RE.Name IS NOT NULL AND RE.Name != '' THEN '.' + RE.Name ELSE '' END,
									CASE WHEN R.Name IS NOT NULL AND R.Name != '' THEN ', район ' + R.Name + ', ' ELSE '' END,
									CASE WHEN C.CityType IS NOT NULL AND C.CityType != ', ' THEN C.CityType ELSE '' END,
									CASE WHEN C.Name IS NOT NULL AND C.Name != '' THEN '.' + C.Name ELSE '' END,
									CASE WHEN ST.Name IS NOT NULL AND ST.Name != '' THEN ', ' + ST.Name ELSE '' END,
									CASE WHEN PA.House IS NOT NULL AND PA.House != '' AND PA.House != '0' THEN ', ' + PA.House ELSE '' END,
									CASE WHEN PA.Block IS NOT NULL AND PA.Block != '' AND PA.Block != '0' THEN ', ' + PA.Block ELSE '' END,
									CASE WHEN PA.Flat IS NOT NULL AND PA.Flat != '' AND PA.Flat != '0' THEN ', ' + PA.Flat ELSE '' END) 
							END
FROM T_PATIENT_ADDRESS PA
			LEFT JOIN AKUZ.T_CITY C ON C.CityID = PA.City
			LEFT JOIN AKUZ.T_STREET ST ON ST.StreetID = PA.Street
			LEFT JOIN AKUZ.T_RAJON R ON R.RajonID = C.Rajon
			LEFT JOIN AKUZ.T_REGION AS RE  ON RE.RegionID = R.Region
where pa.addressid = new.addressid;
	
RETURN NEW;
END;
$update_calcaddress$ LANGUAGE plpgsql;


CREATE TRIGGER updateafterinsert_calcaddress 
after INSERT ON AKUZ.T_PATIENT_ADDRESS
FOR EACH ROW
EXECUTE FUNCTION akuz.update_calcaddress();