-- init
\set ECHO none
create table rtest (
	id integer primary key,
	dat text
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "rtest_pkey" for table "rtest"
create table clog (
  id serial,
  op text,
  data text
);
NOTICE:  CREATE TABLE will create implicit sequence "clog_id_seq" for serial column "clog.id"
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure logtriga('kv',
'insert into clog (op, data) values ($1, $2)');
-- simple test
insert into rtest values (1, 'value1');
update rtest set dat = 'value2';
delete from rtest;
select * from clog; delete from clog;
 id | op |              data              
----+----+--------------------------------
  1 | I  | (id,dat) values ('1','value1')
  2 | U  | dat='value2' where id='1'
  3 | D  | id='1'
(3 rows)

-- test new fields
alter table rtest add column dat2 text;
insert into rtest values (1, 'value1');
update rtest set dat = 'value2';
delete from rtest;
select * from clog; delete from clog;
 id | op |              data              
----+----+--------------------------------
  4 | I  | (id,dat) values ('1','value1')
  5 | U  | dat='value2' where id='1'
  6 | D  | id='1'
(3 rows)

-- test field rename
alter table rtest alter column dat type integer using 0;
insert into rtest values (1, '666', 'newdat');
update rtest set dat = 5;
delete from rtest;
select * from clog; delete from clog;
 id | op |            data             
----+----+-----------------------------
  7 | I  | (id,dat) values ('1','666')
  8 | U  | dat='5' where id='1'
  9 | D  | id='1'
(3 rows)

-- test field ignore
drop trigger rtest_triga on rtest;
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure logtriga('kiv',
'insert into clog (op, data) values ($1, $2)');
insert into rtest values (1, '666', 'newdat');
update rtest set dat = 5, dat2 = 'newdat2';
update rtest set dat = 6;
delete from rtest;
select * from clog; delete from clog;
 id | op |              data               
----+----+---------------------------------
 10 | I  | (id,dat2) values ('1','newdat')
 11 | U  | dat2='newdat2' where id='1'
 12 | D  | id='1'
(3 rows)

-- test wrong key
drop trigger rtest_triga on rtest;
create trigger rtest_triga after insert or update or delete on rtest
for each row execute procedure logtriga('vik',
'insert into clog (op, data) values ($1, $2)');
insert into rtest values (1, 0, 'non-null');
insert into rtest values (2, 0, NULL);
update rtest set dat2 = 'non-null2' where id=1;
update rtest set dat2 = NULL where id=1;
update rtest set dat2 = 'new-nonnull' where id=2;
ERROR:  logtriga: Unexpected NULL key value
delete from rtest where id=1;
ERROR:  logtriga: Unexpected NULL key value
delete from rtest where id=2;
ERROR:  logtriga: Unexpected NULL key value
select * from clog; delete from clog;
 id | op |                  data                  
----+----+----------------------------------------
 13 | I  | (id,dat2) values ('1','non-null')
 14 | I  | (id,dat2) values ('2',null)
 15 | U  | dat2='non-null2' where dat2='non-null'
 16 | U  | dat2=NULL where dat2='non-null2'
(4 rows)