Re: Update more than one table

On Tue, Jul 12, 2005 at 17:35:35 +0200,
  Roman Neuhauser <neuhauser@sigpipe.cz> wrote:
> # bruno@wolff.to / 2005-07-12 10:08:37 -0500:
> > On Sun, Jul 10, 2005 at 15:05:30 -0300,
> >   David Pratt <fairwinds@eastlink.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.

I didn't claim that your approach was wrong. The issue is trying to reduce
the number of round trips by eliminating the two selects.