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
