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(at)eastlink(dot)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
>