Database Reference
In-Depth Information
UPDATE accounts SET amount = amount - 14.00
WHERE owner = 'Bob';
UPDATE accounts SET amount = amount + 14.00
WHERE owner = 'Mary';
COMMIT;
But did Mary actually have an account? If she did not, the last
UPDATE
will succeed
by updating zero rows. If any of the checks fail, you should do a
ROLLBACK
instead
of
COMMIT
. Once you have done all this for all the clients that transfer money, a new
requirement will invariably arrive. Perhaps, the minimum amount that can be trans-
ferred is now
5.00
. You will need to revisit all your code in all your clients again.
So what can you do to make all of this more manageable, more secure, and more
robust? This is where server programming, executing code on the database server
itself, can help. You can move the computations, checks, and data manipulations en-
tirely into a User-defined function (UDF) on the server. This does not just ensure that
you have only one copy of operation logic to manage, but also makes things faster by
not needing several round-trips between client and server. If required, you can also
make sure that only as much information as needed is given out of the database. For
example, there is no business for most client applications to know how much money
Bob has on his account. Mostly, they only need to know if there is enough money to
make the transfer, or more to the point, if the transaction succeeded.
Using PL/pgSQL for integrity checks
PostgreSQL includes its own programming language named PL/pgSQL that is aimed
to integrate easily with SQL commands. PL stands for programming language, and
this is just one of the many languages available for writing server code. pgSQL is
shorthand for PostgreSQL.
Unlike basic SQL, PL/pgSQL includes procedural elements, like the ability to use
if
/
then
/
else
statements and loops. You can easily execute SQL statements, or
even loop over the result of a SQL statement in the language.
The integrity checks needed for the application can be done in a PL/pgSQL function
which takes three arguments: names of the payer and recipient, and the amount to
pay. This sample also returns the status of the payment:
