From: | Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> |
---|---|
To: | David Pratt <fairwinds(at)eastlink(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Update more than one table |
Date: | 2005-07-10 17:32:11 |
Message-ID: | 20050710173211.GA11076@isis.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
# 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
From | Date | Subject | |
---|---|---|---|
Next Message | David Pratt | 2005-07-10 18:05:30 | Re: Update more than one table |
Previous Message | Greg Sabino Mullane | 2005-07-10 14:12:43 | Re: Which record causes referential integrity violation on delete |