I have the following structure:
create table _base (
abbreviation varchar(10),
short_name varchar(30),
full_name varchar(60)
);
create table tome ( id serial ) inherits ( _base );
create table book ( id serial ) inherits ( _base );
create table chapter ( id serial ) inherits ( _base );
create table tome_book (
tome_id int4 references tome ( id ),
book_id int4 references book ( id )
);
create table book_chapter (
book_id int4 references book ( id ),
chapter_id int4 references chapter ( id )
);
create table verse (
id serial,
chapter_id int4 references chapter ( id ),
number varchar(10),
body text
);
create unique index tome_abbr_idx on tome ( abbreviation );
create unique index book_abbr_idx on book ( abbreviation );
create unique index tome_book_idx on tome_book ( tome_id, book_id );
create unique index chapter_abbr_idx on chapter ( abbreviation );
create unique index book_chapter_idx on book_chapter ( book_id, chapter_id );
create view verses as
select t.abbreviation as tome_abbr, t.short_name as tome_short, t.full_name as tome_full,
b.abbreviation as book_abbr, b.short_name as book_short, b.full_name as book_full,
c.abbreviation as chap_abbr, c.short_name as chap_short, c.full_name as chap_full,
v.number, v.body
from tome t, tome_book tb, book b, book_chapter bc, chapter c, verse v
where (tb.tome_id=t.id)
and (tb.book_id=b.id)
and (bc.book_id=b.id)
and (bc.chapter_id=c.id)
and (v.chapter_id=c.id);
create rule insert_verses as
on insert to verses
do instead (
insert into tome ( abbreviation, short_name, full_name )
values ( new.tome_abbr, new.tome_short, new.tome_full );
insert into book ( abbreviation, short_name, full_name )
values ( new.book_abbr, new.book_short, new.book_full );
insert into tome_book ( tome_id, book_id )
values ( currval('tome_id_seq'), currval('book_id_seq') );
insert into chapter ( abbreviation, short_name, full_name )
values ( new.chap_abbr, new.chap_short, new.chap_full );
insert into book_chapter ( book_id, chapter_id )
values ( currval('book_id_seq'), currval('chapter_id_seq') );
insert into verse ( chapter_id, number, body )
values ( currval('chapter_id_seq'), new.number, new.body );
);
I would like to be able to ignore duplicate key entries and continue with the
remainder of the insertion.
For example, if tome already exists then I'll get a 'can't insert duplicate
value' error. I'd like to ignore it and just add the remaining book, chapter
and verse values as well as adding the tome_book and book_chapter entries.
I'm kind of stumped ... any ideas?
Thanks.
Alan
