Karl Nack wrote:
I've been following a few blogs
(http://database-programmer.blogspot.com/,
http://thehelsinkideclaration.blogspot.com/) that make a very compelling
argument, in my opinion, to move as much business/transactional logic as
possible into the database, so that client applications become little
more than moving data into and out of the database using a well-defined
API, most commonly (but not necessarily) through the use of stored
procedures.
I strongly agree with that design philosophy. One principle is that the buck
stops with the database and that regardless of what the application does, any
business logic should be enforced by the database itself. Another principle is
to treat the database like a code library, where the tables are its internal
variables and its public API is stored procedures. Using stored procedures
means you can interact with the database from your application in the same way
your application interacts with itself, meaning with parameterized routine calls.
<snip>
To focus on a more concrete example, let's consider adding a financial
transaction to the database. The "traditional" way to do this, with the
business logic in the application layer, leaves us with two steps:
insert the transaction "header", then insert the line items:
BEGIN;
INSERT INTO transaction (id, date, description)
VALUES (1, CURRENT_DATE, 'Transaction 1');
INSERT INTO line_item (transaction_id, account_id, amount)
VALUES (1, 1, 50), (1, 2, -50);
END;
<snip>
Anything intended to be a single transaction can be a single stored procedure.
The code is something like this (out of my head, adjust to make it correct):
FUNCTION financial_trans (trans_id, when, desc, dest_acct, src_acct, amt)
BEGIN
INSERT INTO transaction (id, date, description)
VALUES (trans_id, when, desc);
INSERT INTO line_item (transaction_id, account_id, amount)
VALUES (trans_id, dest_acct, amt), (trans_id, src_acct, -amt);
END;
SELECT financial_trans( 1, CURRENT_DATE, 'Transaction 1', 1, 2, 50 );
But this still falls short, since we're still basically managing the
transaction in the application layer. The holy grail, so to speak, would
be:
SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50),
(2, -50)));
Well, not quite, because specifying the number "50" twice would be ridiculous
for such a non-generic function; you can calculate the "-50" from it in the
function.
Perhaps I just need to spend more time digging through the
documentation, but I really have no idea how to do something like this,
or if it's even possible. I'm really hoping someone can provide an
example, point me to some resources, or even just share their real-world
experience of doing something like this. It would be very much
appreciated.
A general rule of thumb, however you would design a routine in a normal
programming language, try to do it that way in PL/PgSQL, assuming that PL/PgSQL
is a competent language, and then tweak to match what you actually can do.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
