Thread: Need to understand PL/PGSQL, Foreign Key Deferable, and Transactions...

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?

//--------------------------------------------------
CREATE FUNCTION "public"."invoicer" (bigint) RETURNS bigint AS'
DECLARE
    in_acct_id ALIAS FOR $1;

    my_invoice_id BIGINT;
BEGIN
    /* Get a new invoice_id for the row we are going to insert */
    my_invoice_id := NEXTVAL(''invoice_invoice_id_seq'');

    /* Attach all active transactions that belong in this invoice */
    UPDATE transaction SET
        invoice_id = my_invoice_id
    WHERE invoice_id IS NULL
    AND trans_effective_ts < NOW();

    /* There are no transactions at this time */
    IF NOT FOUND THEN
        RAISE EXCEPTION ''No Transactions Exist to Invoice for %.'',
in_acct_id;
    END IF;

    /* Create a new Invoice */
    INSERT INTO invoice (invoice_id, acct_id)
    VALUES (my_invoice_id, in_acct_id);

    /* if that didn''t work, BAIL */
    IF NOT FOUND THEN
        RAISE EXCEPTION ''Could not create invoice.'';
    END IF;

    /* yeah, that worked */
    RETURN (my_invoice_id);
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
//--------------------------------------------------

-- Dante

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).