Database Reference
In-Depth Information
There's more...
Move the whole computation into the database function
If you can pass all the needed information into the database for processing as a database
function, it will run even faster, as you save several roundtrips to database. If you use a PL/
pgSQL function, you also benefit from automatically saving query plans on first call in a
session, and using saved plans in subsequent calls.
So the preceding transaction is replaced by a function in the database as follows:
CREATE OR REPLACE FUNCTION consume_balance (
i_username text, i_amount numeric(10,2), max_credit numeric(10,2),
OUT success boolean, OUT remaining_balance numeric(10,2)) AS
$$
BEGIN
UPDATE accounts SET balance = balance - i_amount
WHERE username = i_username
AND balance - i_amount > - max_credit
RETURNING balance
INTO remaining_balance;
IF NOT FOUND THEN
success := FALSE;
SELECT balance
FROM accounts
WHERE username = i_username
INTO remaining_balance;
ELSE
success := TRUE;
END IF;
END;
$$ LANGUAGE plpgsql;
and you call it simply by running:
SELECT * FROM consume_balance ('bob', 7, 0);
from your client, returning the success variable telling you if there was high enough balance in
Bob's account, and the number telling the balance Bob has left after this operation.
