Skip site navigation (1) Skip section navigation (2)

Re: transaction

From: denis(at)coralindia(dot)com
To: "Bruno Wolff III" <bruno(at)wolff(dot)to>,"H(dot)J(dot) Sanders" <hjs(at)worldonline(dot)nl>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: transaction
Date: 2004-04-21 07:28:56
Message-ID: 003d01c42772$4fc3bf00$3332a8c0@DENIS (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)wolff(dot)to>
To: H.J. Sanders <hjs(at)worldonline(dot)nl>
Cc: <pgsql-sql(at)postgresql(dot)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(at)worldonline(dot)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



In response to

Responses

pgsql-sql by date

Next:From: denisDate: 2004-04-21 08:44:56
Subject: Re: Syntax for cmd to EXEC...how many quotes?
Previous:From: Denis P GohelDate: 2004-04-21 07:11:53
Subject: Re: staggered query?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group