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@xxxxxxxxxxx / 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
