#!/bin/sh

DBNAME=testdb
export DBNAME

dropdb ${DBNAME}
createdb ${DBNAME}

psql -e ${DBNAME} <<_EOF_

create table t1 (
	id1a integer,
	id1b integer,
	
	primary key (id1a, id1b)
);

create table t2 (
	id2a integer,
	id2c integer,
	
	primary key (id2a, id2c)
);

create table t3 (
	id3a integer,
	id3b integer,
	id3c integer,
	data text,

	primary key (id3a, id3b, id3c),

	foreign key (id3a, id3b) references t1 (id1a, id1b),
	foreign key (id3a, id3c) references t2 (id2a, id2c)
);


insert into t1 values (1, 11);
insert into t1 values (1, 12);
insert into t1 values (2, 21);
insert into t1 values (2, 22);

insert into t2 values (1, 11);
insert into t2 values (1, 12);
insert into t2 values (2, 21);
insert into t2 values (2, 22);

insert into t3 values (1, 11, 11, 'row1');
insert into t3 values (1, 11, 12, 'row2');
insert into t3 values (1, 12, 11, 'row3');
insert into t3 values (1, 12, 12, 'row4');
insert into t3 values (1, 11, 13, 'row5');
insert into t3 values (1, 13, 11, 'row6');

create rule t3_ins as on insert to t3
	where (exists (select 1 from t3
			where (((t3.id3a = new.id3a)
			and (t3.id3b = new.id3b))
			and (t3.id3c = new.id3c))))
	do instead update t3 set data = new.data
	where (((t3.id3a = new.id3a)
	and (t3.id3b = new.id3b))
	and (t3.id3c = new.id3c));

insert into t3 values (1, 11, 13, 'row7');
insert into t3 values (1, 13, 11, 'row8');

select * from t3;

select version();
_EOF_