Re: Am I headed in the right direction? (long)

On Wed, May 16, 2001 at 07:57:35PM -0600, Alan Young wrote:
> 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?

looks like a job for pl/pgsql...?

--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting. -- www.cluetrain.com

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!