Re: Update more than one table

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.

Another option that I think could work is to make the two foreign key checks
deferrable and insert the record for table 2 before the two records in
table 1. You can use nextval(pg_get_serial_sequence('table1', 'table1key'))
twice in the insert. Then when inserting the two entries into table 1 you
can use currval to get the key value for the record in table 2 and use the
appropiate column for each of the two records. As long as you aren't
depending on the ordering of the key values for the two records in table 1
you should be OK.