Re: Need to understand PL/PGSQL, Foreign Key Deferable,

On Wed, 17 Dec 2003, D. Dante Lorenso wrote:

> I just wrote a PL/PGSQL function that is working, but I don't know
> why it is...
>
> I have a foreign key constraint defined on:
>
>     transaction.invoice_id --> invoice.invoice_id
>
> But I did NOT state that it was DEFERRABLE.  In this PL/PGSQL
> function below, I update the transaction values and set them
> to the invoice_id that does not yet exist in the invoice table.
>
> I later add the invoice record, so technically at the end of the
> PL/PGSQL function, the constraint is satisfied.  So, what's the
> deal?  The foreign key checks are not done until AFTER the
> function exits?  If this is true, should I rely on this to exist
> into the future as well or do I need to design my function
> differently?

Right now, this is true, because the triggers run at "outer" statement end
(ie the statement that called the function).  I wouldn't want to bet on
it possibly not changing in the future (the spec is difficult enough to
read on these issues that we may find out we're doing it wrong).