[Bioperl-l] Re: [BioSQL-l] PostgreSQL & lookup & insert in bioperl-db

[Hilmar Lapp]

On Thursday, February 27, 2003, at 02:11  AM, Yves Bastide wrote:

> There may be another possibility:
> ybastide=# create table test(test_id serial primary key, name text 
> unique);
> ybastide=# CREATE RULE test_insert as on insert to test where (select 
> oid from test where name=new.name) is not null do instead nothing;
>

Excellent. Let's call this option 4. It is similar to option 3 in that 
resolving whether or not the record exists is deferred to the database 
instead of to acrobatics in the code. It has the same disadvantage of 
decoupling the Pg version of the schema from being auto-translated from 
the MySQL version, which Elia correctly suggested should not be an 
issue. It is (to me) better than option 3 in that 1) the INSERT remains 
an INSERT in sql (so it's even cleaner in terms of least impact on 
bioperl-db hacks), and 2) I don't have to learn writing (and debugging) 
PL/PgSQL right now, but can just copy and paste Yves' example.

Everyone OK with option 4? If not speak now.

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------