RE: MySql 'REPLACE'

From an earlier posting....

Replace into does an insert by default.  If however, there is already a
record which would cause a unique index collision, then mysql does an update
on that record.  I want to prevent my application from having to do all of
that bookkeeping.

Peter Eisentraut has suggested:
begin transaction;
update ...
<if zero rows> insert ...
<endif>
commit;

This seems like a good way to go.  He suggested that I write a PL/pgSQL
function to do the work.  I really don't have time to climb the learning
curve on this so I might just write a perl module to do it, and a few other
things, for me.

Thanx for your help.

If there is AN EVEN EASIER way, please let me know.

Mike Diehl,
Network Monitoring Tool Devl.
284-3137
jdiehl@sandia.gov



> -----Original Message-----
> From: Alessio Bragadini [mailto:alessio@albourne.com]
> Sent: April 25, 2001 6:39 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] MySql 'REPLACE'
> 
> 
> I am working in porting the Slash software from MySql to PostgreSQL. I
> stepped in MySql's REPLACE command (a SQL command) that to my 
> knowledge
> is not supported by PostgreSQL and it's not standard SQL. According to
> MySql's manual:
> 
> "REPLACE works exactly like INSERT, except that if an old 
> record in the
> table has the same value as a new record on a unique index, the old
> record is
> deleted before the new record is inserted. See section 7.21 INSERT
> syntax."
> 
> REPLACE INTO table (column, column...) VALUES (value, value...)
> 
> Has anyone had any experience about how to simulate it under 
> PostgreSQL?
> I am using Perl and I can move most of the thing into application
> anyway.
> 
> Thanks.
> 
> -- 
> Alessio F. Bragadini        alessio@albourne.com
> APL Financial Services        http://village.albourne.com
> Nicosia, Cyprus             phone: +357-2-755750
> 
> "It is more complicated than you think"
>         -- The Eighth Networking Truth from RFC 1925
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>