Re: Implementing "thick"/"fat" databases - Mailing list pgsql-general

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