From: denis@coralindia.com
To: Bruno Wolff III <bruno@wolff.to>
To: H.J. Sanders <hjs@worldonline.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: transaction
Date: Wed, 21 Apr 2004 12:58:56 +0530
Message-ID: <003d01c42772$4fc3bf00$3332a8c0@DENIS> (raw)
References: <KIEBKBGIGGNDKKJJNKCFOELJCJAA.hjs@worldonline.nl>
<20040421020010.GA19910@wolff.to>
Hi,
You can achieve this by:
1. Create a new table
2. Insert the data in this.
3. Write a trigger on this table
4. In trigger issue UPDATE and check whether it updated any records. If NO,
fire INSERT.
( here, i am updating first and inserting.. just reverse )
The code looks like:
update tempxitag set qty = qty + nqty where
ccod = cccod
GET DIAGNOSTICS nFound = ROW_COUNT;
If nFound = 0 then
insert into tempxitag( ccod, qty)
values (cccod, nqty );
End if;
HTH
Denis
----- Original Message -----
From: Bruno Wolff III <bruno@wolff.to>
To: H.J. Sanders <hjs@worldonline.nl>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 21, 2004 7:30 AM
Subject: Re: [SQL] transaction
> On Tue, Apr 20, 2004 at 21:14:48 +0200,
> "H.J. Sanders" <hjs@worldonline.nl> wrote:
> >
> > Hello list.
> >
> > We are migrating from Informix to PSQL.
> >
> > In Informix we used to do:
> >
> > - BEGIN WORK
> >
> > - INSERT ROW
> >
> > - IF FAILED THEN UPDATE ROW
> >
> > - COMMIT WORK
> >
> >
> > In PSQL this does not seem to work because after the first error (the
> > insert)
> > everything is omitted.
> >
> > Has someone found a solution for this (we have 1000's of this type).
>
> This question has come up a few times over the last year and there isn't
> a great answer. Locking the table is a simple solution, but can lead to
> problems because of contention.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: denis@coralindia.com, bruno@wolff.to, hjs@worldonline.nl
Subject: Re: transaction
In-Reply-To: <003d01c42772$4fc3bf00$3332a8c0@DENIS>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox