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

Karl Nack wrote:
>>> 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.
>
> Not if there were more than two line-items per transaction. A paycheck
> is a good example, where generally one or more income accounts are
> credited and multiple tax accounts as well as one or more asset accounts
> are debited. Ensuring that all the line-items add up to 0 would be one
> of the data integrity rules implemented in the database (though most
> likely checked in the application layer as well).

It would help if you clarified your intent with another example.

I was assuming here that we were dealing with a double-entry accounting system
where every line item in one account had a corresponding line item in another
account of equal magnitude, and so all line items were in pairs, because you
showed what looked like 1 pair, hence specifying the "50" once makes sense.

So are you wanting the 1 function to take a set of line-item pairs, or are you
wanting to say do +50 in one account and -25 in each of 2 different accounts, as
a total of 3 line items?  If the former, I would still just specify the "50"
once and have a set of {from, to, amount} triples as an argument, which would be
relation/rowset-typed.  If the latter, then you would specify the "50" twice.

>> 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.
>
> In the language I'm most familiar with, PHP, I could do this with an
> associative array:
>
> $transaction = array(
>     'id' => 1,
>     'date' => date('Y-m-d'),
>     'description' => 'Transaction 1',
>     'line_items' => array(
>         array('account_id' => 1, 'amount' => 50),
>         array('account_id' => 2, 'amount' => -50),
>     ),
> );
>
>From which I can easily build the appropriate SQL statements. This would
> be very similar in Python. I wonder if this would be achievable in
> PL/PGSQL, maybe through the use of composite types and/or domains?

Yes, you could.  PL/PgSQL supports relation/rowset-typed arguments (declared as
"TABLE OF <rowtype>" or something like that).

Or alternately you could use temporary staging tables as quasi-arguments rather
than using an actual argument.

-- Darren Duncan