On Wednesday 25 Jun 2003 9:40 am, Bjorn T Johansen wrote:
[snip]
> 1. I can delete every row for the order on every update and do an insert
> instead.
>
> 2. or I can write a function (stored proc) that first tries to update
> and if update fails, it then does an insert.
>
> The first one I know how to do (don't know how much penalty this would
> cause?) and the second one is perhaps the best way to do it, but since I
> have never written any stored proc for PostgreSQL yet, I need some
> pointers on how to implement this...
Someone else has an example showing a test/update combo, but one thing you
should be aware of is that with PG an update is effectively a delete+insert
anyway. This is part of the MVCC system so other transactions can still see
the old values while you're going about doing your updates.
I'd just wrap the delete/insert in a transaction and get back to coding my
app.
--
Richard Huxton