On 22 Jul 2011, at 21:15, Karl Nack wrote:
> But this still falls short, since we're still basically managing the
> transaction in the application layer.
The problem you're facing here is that database statements work with records,
while your example has a need to handle a set of (different types of) records
in one go.
> The holy grail, so to speak, would be:
>
> SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50),
> (2, -50)));
Now imagine someone not familiar with your design reading this query...
To start with, they're going to assume this query SELECTs data, while it
actually inserts it. Secondly, it's impossible to see what the different
data-values are supposed to represent without looking up the function - and
quite possibly, it's implementation. They're going to wonder what (1,50) and
(2, -50) mean, what kind of date current_date gets assigned to, etc.
Having to write queries like these is even worse, even if you designed the
function. You'll be looking at your own documentation a lot while writing these.
It would seem to me that the API you would provide for business logic like this
should provide the users of said API with enough context to create valid
statements. For example, you could use XML to describe the data (I'm no fan of
XML, but it does suit a need here and allows validation of the provided data),
especially as Postgres has XML parsing functionality.
Or you could use a more sophisticated procedural language (plpython or plphp,
for example) that's capable of marshalling and unmarshalling data structures to
strings and vice versa (eg. '{foo:1,bar:2}').
You would still have a SELECT statement that INSERTs data, which is
semantically a bad thing to do IMHO.
Perhaps the better solution is (as others mentioned already) to move the data
interpretation to a (web)service/application server and have that perform the
actual database operations.
With that in mind, you would put business logic ("process an invoice") into an
"application server", while you put data integrity logic ("don't allow
transactions with no line_items") into the database.
Alban Hertroys
--
The scale of a problem often equals the size of an ego.
!DSPAM:737,4e2a9c2112098024710106!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
