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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
