ALTER TABLE AKUZ.T_PATIENT_ADDRESS ADD "calcaddress" varchar(2000) NULL;

UPDATE AKUZ.T_PATIENT_ADDRESS PA
    SET FreeAddressString = 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 AKUZ.T_CITY C
			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 RE ON RE.RegionID = R.Region
        where C.CityID = PA.City;

CREATE INDEX t_patient_address_calcaddress_fti ON T_PATIENT_ADDRESS USING gin(to_tsvector('russian', CalcAddress));
