1

How to aggregate transactions to form a stateful view which can be updated efficiently?

For example how to turn the following transactions:

CREATE TABLE txns_combined(from_party,to_party,price,qty,item) AS
VALUES
        ( 'Tom'  , 'Sam'   , 10    , 2        , 'Book' ),
        ( 'Sam'  , 'Jerry' , 2     , 1        , 'Pen' ),
        ( 'Bill' , 'Sam'   , 1     , 3        , 'Pencil' ),
        ( 'Matt' , 'Sam'   , 1.5   , 4        , 'Pencil' );

Into positions and update the below table instead of recalculate it each time a new transaction is added:

+-------+--------+----------+---------------+
| Name  | Item   | Quantity | Average Price |
+-------+--------+----------+---------------+
| Tom   | Book   | -2       | 10            |
+-------+--------+----------+---------------+
| Sam   | Book   | 2        | 10            |
+-------+--------+----------+---------------+
| Sam   | Pen    | -1       | 2             |
+-------+--------+----------+---------------+
| Jerry | Pen    | 1        | 2             |
+-------+--------+----------+---------------+
| Sam   | Pencil | 7        | 1.29          |
+-------+--------+----------+---------------+
| Bill  | Pencil | -3       | 1             |
+-------+--------+----------+---------------+
| Matt  | Pencil | -4       | 1.5           |
+-------+--------+----------+---------------+

Should LISTEN, NOTIFY or views be used? I am using Postgres 10 and have the option to use TimescaleDB.

  • you just want a transaction that rewrites one entry from the top into two entries for the bottom with two INSERT .. ON CONFLICT DO UPDATE – Evan Carroll Aug 1 '18 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.