Am I headed in the right direction? (long)
От | Alan Young |
---|---|
Тема | Am I headed in the right direction? (long) |
Дата | |
Msg-id | 01051620082900.03112@harleypig.idiglobal.com обсуждение исходный текст |
Ответы |
Re: Am I headed in the right direction? (long)
|
Список | 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
В списке pgsql-general по дате отправления: