
BEGIN;

-- translate the pair of columns, boolean pets and text pets conditions to one single int pets code column.
alter table unit add column petscode int;

CREATE OR REPLACE FUNCTION fix_pets() RETURNS VOID as '
DECLARE
	u RECORD;
	NO int := 1;
	YES int := 2;
	OUTSIDE_ONLY int := 3;
	SEE_COMMENTS int := 4;
	pc int;
BEGIN
	FOR u in SELECT id, pets, petsconditions from unit LOOP
		pc := 0;
		if u.pets = FALSE THEN
			pc := NO;	-- pretty straightforward.
		ELSE
			IF u.petsconditions = ''Yes'' or u.petsconditions is null THEN
				pc := YES;
			ELSEIF u.petsconditions like ''Outside%'' THEN
				pc := OUTSIDE_ONLY;
			ELSEIF u.petsconditions like ''See%'' THEN
				pc := SEE_COMMENTS;
			END IF;
		END IF;	-- if pets = FALSE
		
		-- did we determine a good value?
		IF pc = 0 THEN
			raise exception ''No code determined for unit %, %, "%"'', u.id, u.pets, u.petsconditions;
		END IF;
		
		-- ok -- now reassign.
		UPDATE unit SET petscode = pc where id = u.id;
	END LOOP;
	
END;
' language plpgsql;


select fix_pets();
drop function fix_pets();

alter table unit alter petscode set not null;

alter table unit drop column pets;
alter table unit drop column petsconditions;

COMMIT;
