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

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need to understand PL/PGSQL, Foreign Key Deferable, and Transactions...
Date: 2003-12-17 11:23:42
Message-ID: 3FE03CBE.10308@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2003-12-17 11:36:20 Re: Firebird and PostgreSQL at the DB Corral.
Previous Message Paul Ganainm 2003-12-17 11:08:13 Re: Firebird and PostgreSQL at the DB Corral.