Re: Implementing "thick"/"fat" databases

>
> Now, if you are doing double-entry bookkeeping this doesn't provide
> enough consistency, IMO. You can't check inside the function to
> ensure that the transaction is balanced. it would be better to:
>
> BEGIN;
> SELECT create_transaction(1, current_date, 'Transaction 1', '{{1, 1,
> 50},{1,1,-50}}');
> COMMIT;
>
> Now for the application, you can create an API that is semantically
> clearer. But PostgreSQL doesn't provide an easy way of calling
> procedures of this sort out of select/update/insert statements and
> select is the only way to do this.
>
>

One option to consider is restricting final tables but making staging tables available. You use normal inserts to build up the staging table and then validate and transfer the data to the final table using a function. In the example you can build a work-in-process transaction however you see fit but the final real transaction creation process would first confirm that the entry balances before copying the records to the transaction table and cleaning up the work-in-process table.

David J.