Am I headed in the right direction? (long)

From: Alan Young <alany(at)harleypig(dot)idiglobal(dot)com>
To: PG-General <pgsql-general(at)postgresql(dot)org>
Subject: Am I headed in the right direction? (long)
Date: 2001-05-17 01:57:35
Message-ID: 01051620082900.03112@harleypig.idiglobal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joel Stevenson 2001-05-17 02:27:47 Explicit column naming on INSERT question
Previous Message Lyle Lee 2001-05-17 01:55:30 NULL indicator variable