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

[Yves Bastide]

Hilmar Lapp wrote:
> 
> 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.

Yep, let's hope it will work :)
Just one comment: the test should SELECT the primary key, not the oid 
(which may not exist, is not indexed, and can be non-unique)

> 
> Everyone OK with option 4? If not speak now.
> 
>     -hilmar

yves