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