I have never tested a particular scenario like this out, but would AFTER INSERT triggers resolve this issue for you?
On Wednesday 15 December 2004 11:53 am, Larry White saith: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
