By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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).

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.