Re: transactions, functions, foreign keys

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: terry@esc1.com