Need to understand PL/PGSQL, Foreign Key Deferable, and Transactions...
От | D. Dante Lorenso |
---|---|
Тема | Need to understand PL/PGSQL, Foreign Key Deferable, and Transactions... |
Дата | |
Msg-id | 3FE03CBE.10308@lorenso.com обсуждение исходный текст |
Ответы |
Re: Need to understand PL/PGSQL, Foreign Key Deferable,
|
Список | 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
В списке pgsql-general по дате отправления: