Re: Implementing "thick"/"fat" databases

From: Darren Duncan
Subject: Re: Implementing "thick"/"fat" databases
Date: ,
Msg-id: 4E2B5204.1060101@darrenduncan.net
(view: Whole thread, Raw)
In response to: Implementing "thick"/"fat" databases  ("Karl Nack")
Responses: Re: Implementing "thick"/"fat" databases  (David Johnston)
List: pgsql-general

Tree view

Implementing "thick"/"fat" databases  ("Karl Nack", )
 Re: Implementing "thick"/"fat" databases  (Darren Duncan, )
  Re: Implementing "thick"/"fat" databases  (John R Pierce, )
   Re: Implementing "thick"/"fat" databases  (Darren Duncan, )
   Re: Implementing "thick"/"fat" databases  (Gavin Flower, )
    Re: Implementing "thick"/"fat" databases  (Chris Travers, )
     Re: Implementing "thick"/"fat" databases  (Gavin Flower, )
     Re: Implementing "thick"/"fat" databases  (Merlin Moncure, )
      Re: Implementing "thick"/"fat" databases  (Chris Travers, )
       Re: Implementing "thick"/"fat" databases  (Merlin Moncure, )
        Re: Implementing "thick"/"fat" databases  (Vincent Veyron, )
  Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
 Re: Implementing "thick"/"fat" databases  (Alban Hertroys, )
  Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
 Re: Implementing "thick"/"fat" databases  (Chris Travers, )
  Re: Implementing "thick"/"fat" databases  (David Johnston, )
   Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
  Re: Implementing "thick"/"fat" databases  (David Johnston, )
   Re: Implementing "thick"/"fat" databases  (Chris Travers, )
  Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
   Re: Implementing "thick"/"fat" databases  ("Karsten Hilbert", )
    Re: Implementing "thick"/"fat" databases  (Chris Travers, )
 Re: Implementing "thick"/"fat" databases  (Darren Duncan, )
  Re: Implementing "thick"/"fat" databases  (David Johnston, )
 Re: Implementing "thick"/"fat" databases  (Chris Travers, )
  Re: Implementing "thick"/"fat" databases  (Chris Travers, )
  Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
 Re: Implementing "thick"/"fat" databases  (Sim Zacks, )
  Re: Implementing "thick"/"fat" databases  (Chris Travers, )
   Re: Implementing "thick"/"fat" databases  (Sim Zacks, )
    Re: Implementing "thick"/"fat" databases  (Chris Travers, )
     Re: Implementing "thick"/"fat" databases  (Sim Zacks, )
      Re: Implementing "thick"/"fat" databases  (Chris Travers, )
 Re: Implementing "thick"/"fat" databases  (Frank Lanitz, )
  Re: Implementing "thick"/"fat" databases  (Pavel Stehule, )
   Re: Implementing "thick"/"fat" databases  (Frank Lanitz, )
  Re: Implementing "thick"/"fat" databases  (Sim Zacks, )
   Re: Implementing "thick"/"fat" databases  (Frank Lanitz, )
   Re: Implementing "thick"/"fat" databases  (Chris Travers, )
 Re: Implementing "thick"/"fat" databases  (Chris Travers, )
  Re: Implementing "thick"/"fat" databases  (Merlin Moncure, )
   Re: Implementing "thick"/"fat" databases  (Chris Travers, )
    Re: Implementing "thick"/"fat" databases  ("Karsten Hilbert", )
     Re: Implementing "thick"/"fat" databases  (Peter Bex, )
    Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
  Re: Implementing "thick"/"fat" databases  ("Karl Nack", )
   Re: Implementing "thick"/"fat" databases  (Sim Zacks, )

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


pgsql-general by date:

From: Yan Chunlu
Date:
Subject: Re: streaming replication does not work across datacenter with 20ms latency?
From: Sean Moss-Pultz
Date:
Subject: installation problems on OSX Lion