do $$
declare
	_type integer := (select e.EntityID from vclib.T_ENTITIES e where e.EntName = 'RECIPE_FORM' and e.schema = 'AKUZ' limit 1);
	_sql text;
begin
    drop index if exists akuz.UQ_T_RECIPE_SerialNumberDeleted_recipeForm;


    ALTER TABLE AKUZ.T_RECIPE
    DROP COLUMN IF EXISTS year;


    ALTER TABLE akuz.t_recipe
    ADD COLUMN Year integer;

    UPDATE akuz.t_recipe
    SET Year = EXTRACT(YEAR FROM RecipeDate);

    ALTER TABLE akuz.t_recipe
    ALTER COLUMN Year SET NOT NULL;

	_sql := 'CREATE UNIQUE INDEX IF NOT EXISTS UQ_T_RECIPE_SerialNumberDeleted_recipeForm ON AKUZ.T_RECIPE(Serial, Number, Year) where Deleted = false and MedicalRecordType = ' || _type::text;

	execute _sql;
end;
$$ language plpgsql;