Hi,
I've run into a situation (I should have forseen) and was hoping
someone could show me a way out.
I have a function that calls other functions. These other functions
are inserting rows and return the primary key for the inserted row.
Some of the tables are related in a way that they have a foreign key
reference to a table that was updated in a previous step.
Here's an example in psuedocode
create function foo() AS '
begin
select into key1 bar1( a, b);
select into key2 bar2,(e, f, key1);
etc...
end
'
The call to bar2 uses the key from the call to bar1. The table
updated in bar2 has a foreign key constraint referencing the key1
column from bar1, but the bar1 transaction hasn't been committed.
Thus - a foreign key violation exception. (That's the part I should
have seen coming.)
Is there anyway to cleanly handle this kind of situation? I'm
working on the initialization piece of a fairly complex database and
there are a large number of these relations to setup.
I'd prefer not to have to call each separately from the command line
because of the possibility of error. They could also be called
sequentially in a .sql file, but there's no way to pass variables
between them then.
Thanks for your help.