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