/*
psql:dbUpdates.sql:6: ERROR:  cache lookup failed for function 0
CONTEXT:  SQL statement "SELECT   $1  is not null and  $2  not in (select id from housing.unpublished_units)"
PL/pgSQL function "unit_manage_geocode" line 25 at if
psql:dbUpdates.sql:7: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
*/

begin;

create table t1
(
	id int primary key,
	val text not null default ''
);

create table skip_t1_ids
(
	-- don't track changes in t2 for these t1 rows.
	t1_id int references t1(id) on delete cascade
);

create sequence t2_seq;
create table t2
(
	id int not null default nextval('t2_seq'),
	t1_id int references t1(id) on delete cascade,
	updated timestamp not null default now(),
	old_val text not null
);

create or replace function track_t1_changes() returns trigger as
$$
BEGIN
	if NEW.val <> OLD.val and NEW.id not in (select t1_id from skip_t1_ids)  then
		insert into t2 (t1_id, old_val) values (NEW.id, NEW.val);
	end if;
	RETURN null;
END;
$$ language plpgsql;

create trigger track_t1_changes_t  AFTER UPDATE on t1
	FOR EACH ROW EXECUTE PROCEDURE track_t1_changes();
	
insert into t1 (id, val) values (1, 'first version');
insert into t1 (id, val) values (2, 'first version');
insert into t1 (id, val) values (3, 'first version');

insert into skip_t1_ids values (2);
insert into skip_t1_ids values (3);

update t1 set val = 'new value now' where id = 1;

commit;
