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.
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.
Can someone suggest the best way of handling this. Triggers are out
since each update requires different fields. I am thinking the only
way to do this is a function. So biggest question is how to return the
ids created from the first update (so they can be used by the second)
and then need the id generated from second update (so it can be used
the third). The ids for each table are serial type so they each have a
sequence associated with them. Would you nest functions? Create each
separately and wrap them within one function? Even with this, I am
thinking the most important part is how do I return the id from the
record I just inserted in a table.
Many thanks
David
(end of broadcast)
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sun, Jul 10, 2005 at 15:05:30 -0300,
David Pratt <fairwinds (AT) eastlink (DOT) cawrote:
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.

Normally you can just use currval. But in your case you insert insert two
records and currval will only return the value of the second record's key.
Assuming the first record's key is one less than the second's is not a good
idea. With the current version you can probably make this work reliably
by grabbing a block of ids for your session and making sure that the two
records get their keys from the same preallocated block.

Another option that I think could work is to make the two foreign key checks
deferrable and insert the record for table 2 before the two records in
table 1. You can use nextval(pg_get_serial_sequence('table1', 'table1key'))
twice in the insert. Then when inserting the two entries into table 1 you
can use currval to get the key value for the record in table 2 and use the
appropiate column for each of the two records. As long as you aren't
depending on the ordering of the key values for the two records in table 1
you should be K.

(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Tue, Jul 12, 2005 at 17:35:35 +0200,
Roman Neuhauser <neuhauser (AT) sigpipe (DOT) czwrote:
# bruno (AT) wolff (DOT) to / 2005-07-12 10:08:37 -0500:
Sun, Jul 10, 2005 at 15:05:30 -0300,
David Pratt <fairwinds (AT) eastlink (DOT) cawrote:
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.

Normally you can just use currval. But in your case you insert insert two
records and currval will only return the value of the second record's key.
Assuming the first record's key is one less than the second's is not a good
idea. With the current version you can probably make this work reliably
by grabbing a block of ids for your session and making sure that the two
records get their keys from the same preallocated block.

Notice the pseudo code I posted:

INSERT INT first_table ; <- insert one row
SELECT currval(first_table); <- first currval()
INSERT INT first_table ; <- insert another row
SELECT currval(first_table); <- second currval()
INSERT INT second_table ; <- this ellipsis hides the two
currval() return values

See? I didn't assume anything.

I didn't claim that your approach was wrong. The issue is trying to reduce
the number of round trips by eliminating the two selects.

(end of broadcast)
TIP 5: don't forget to increase your free space map settings