Re: Update more than one table

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.

Regards,
David

On Sunday, July 10, 2005, at 02:32 PM, Roman Neuhauser wrote:

> # fairwinds@eastlink.ca / 2005-07-09 22:55:26 -0300:
>> Hi. I have a form that collects information from the user but then I
>> need to update three separate tables from what the user has submitted.
>> I could do this with application logic but I would feel it would be
>> best handled in Postgres as a transaction.
>
>     Those two don't conflict.
>
>> I need to do things in this order to satisfy the foreign key
>> constraints:
>>
>> 1.   Insert part of the data into 2 records of the first table (I need
>> to return theses ids so available for the next insert).
>>
>> 2.   Insert part of the data into a record in a second table.  The
>> id's
>> created in 1. need to be part of this record (cannot be null values)
>> and have also have referential integrity with the first table
>>
>> 3.   Insert the last part of the data into a record in a third table.
>> The id created in 2 needs to be part of this record). This has
>> referential integrity with the second table.
>
>     metacode:
>
>     BEGIN;
>     INSERT INTO first_table ...;
>     SELECT currval(first_table);
>     INSERT INTO first_table ...;
>     SELECT currval(first_table);
>     INSERT INTO second_table ...;
>     INSERT INTO third_table (... currval(second_table));
>     COMMIT;
>
>     You can do this with any CLI, like libpq, the Perl DBI, PHP/PEAR
>     pgsql_* functions or DB...
>
> --
> 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
>