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. Although there seems to be a lot of discussion out there of
the reasons why one might want to do this, I'm really at a loss for
finding good, concrete examples of how to do it. Consequently, I'm
hoping that somebody can share their experience(s), or point me to some
examples, of doing this with PostgreSQL. I'd consider myself fairly
well-versed in using the various features of PostgreSQL to enforce data
integrity, but much less so for implementing transactional logic.
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;
Now if we start moving this logic to the database, we'd have something
like:
BEGIN;
SELECT create_transaction(1, current_date, 'Transaction 1');
SELECT create_line_item(1, 1, 50);
SELECT create_line_item(1, 1, -50);
END;
But we've actually taken a step back, since we're making a round-trip to
the database for each line item. That could be resolved by doing:
BEGIN;
SELECT create_transaction(1, current_date, 'Transaction 1');
SELECT create_line_item(transaction_id, account_id, amount)
FROM (VALUES (1, 1, 50), (1, 2, -50))
AS line_item (transaction_id, account_id, amount);
END;
Better, but still not good, since we're invoking the function for each
individual line item, which ultimately means separate INSERTs for each
one. What we'd want is something like:
BEGIN;
SELECT create_transaction(1, current_date, 'Transaction 1');
SELECT create_line_items(((1, 1, 50), (1, 2, -50)));
END;
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)));
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.
Thanks.
Karl Nack
Futurity, Inc
5121 N Ravenswood Ave
Chicago, IL 60640
773-506-2007
