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

[Yves Bastide]

Hilmar Lapp wrote:
> 3) Separate the look-up into PL/PgSQL code in the database. Instead of 
> issuing a direct INSERT you call a stored procedure that looks up the 
> entry and inserts it if not yet present. The advantage of this option is 
> that it keeps the bioperl-db layout largely unaffected and hence treats 
> all RDBMSs relatively equal (i.e., cares least about RDBMS 
> idiosyncrasies). The downside is that this means that the Pg version of 
> Biosql can't be fully auto-generated anymore from the MySQL version (I 
> don't want to auto-generate the PL/PgSQL procedures from a MySQL schema 
> definition). Also, people say PL/PgSQL is not the fastest thing out there.
> 
> Let me know your thoughts and votes. There may easily be more options, 
> feel free to share.

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;
ybastide=# INSERT INTO test (name) values('x');
INSERT 593248 1
ybastide=# INSERT INTO test (name) values('x');
INSERT 0 0
ybastide=#

Comments?

> 
>     -hilmar

yves