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

Re: Update more than one table

From: Roman Neuhauser <neuhauser(at)sigpipe(dot)cz>
To: Bruno Wolff III <bruno(at)wolff(dot)to>,David Pratt <fairwinds(at)eastlink(dot)ca>, pgsql-general(at)postgresql(dot)org
Subject: Re: Update more than one table
Date: 2005-07-12 15:35:35
Message-ID: 20050712153535.GD5701@isis.sigpipe.cz (view raw or flat)
Thread:
Lists: pgsql-general
# bruno(at)wolff(dot)to / 2005-07-12 10:08:37 -0500:
> On Sun, Jul 10, 2005 at 15:05:30 -0300,
>   David Pratt <fairwinds(at)eastlink(dot)ca> wrote:
> > Hi Roman.  Many thanks for your reply.  This is interesting and will I 
> > give this a try and let you know how it works out. With this you are 
> > right, application logic and transaction don't have to be separate 
> > which would be nice for this.  I was thinking the only way to solve was 
> > a function that performed an update and returned the nextval at the 
> > same time so that I could use that value to perform the update on next 
> > table,etc.
> 
> Normally you can just use currval. But in your case you insert insert two
> records and currval will only return the value of the second record's key.
> Assuming the first record's key is one less than the second's is not a good
> idea. With the current version you can probably make this work reliably
> by grabbing a block of ids for your session and making sure that the two
> records get their keys from the same preallocated block.

    Notice the pseudo code I posted:

    INSERT INTO first_table ...;  <- insert one row
    SELECT currval(first_table);  <- first currval()
    INSERT INTO first_table ...;  <- insert another row
    SELECT currval(first_table);  <- second currval()
    INSERT INTO second_table ...; <- this ellipsis hides the two
                                     currval() return values

    See? I didn't assume anything.


-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

In response to

Responses

pgsql-general by date

Next:From: Adam O'TooleDate: 2005-07-12 15:47:57
Subject: INSERT INTO from a SELECT query
Previous:From: Alex StapletonDate: 2005-07-12 15:21:54
Subject: Re: utf-8 and cultural sensitive sorting

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