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(at)sandia(dot)gov

> -----Original Message-----
> From: Alessio Bragadini [mailto:alessio(at)albourne(dot)com]
> Sent: April 25, 2001 6:39 AM
> To: pgsql-sql(at)postgresql(dot)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(at)albourne(dot)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
>