Re: Update more than one table

# 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