Implementing system-versioned tables in Postgres(hypirion.com)

    * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE"
    * OperationTimestamp: TIMESTAMPTZ
    * OperationId: BIGSERIAL (needed to can handle cases where a row is modified multiple times in a transaction)

    BEGIN;
    WITH
      x_lineitems AS
         SELECT li.id, pt.id pt_id, li.qty, (li.qty * pt.cost) ext_cost
            FROM (VALUES (${lineitem1}, ${qty1}),
                         (${lineitem2}, ${qty2})
            AS li (id, qty)
      x_order AS
         INSERT INTO orders (id, total_cost)
           SELECT ${order_id},
                  uuid_generate_v7(),
                  (SELECT sum(ext_cost) FROM x_lineitems)
           RETURNING id, version_id
    -- Line items get same version ID as the order.
    INSERT INTO line_items (id, version_id, order_id, part_id, qty)
      (SELECT xli.id, xord.version_id, xord.id, xli.part_id, xli.qty}
          FROM x_lineitems xli, x_order xord);

    -- Triggers on orders and line_items fire, still as part of
    -- the transaction.

    INSERT INTO order_history (id, version_id, total_cost)
       SELECT o.id, o.version_id, o.total_cost
         FROM orders o WHERE o.id = ${order_id};

    INSERT INTO line_items_history
       (id, version_id,
        order_id, order_version_id,
        part_id, part_version_id,
        qty)
       SELECT l.id, l.version_id,
              o.id, o.version_id,
              p.id, p.version_id,
              l.qty
         FROM line_items l JOIN orders o JOIN parts p
         WHERE l.order_id = ${order_id}
           AND o.id = ${order_id}
           AND p.id = l.part_id;

    -- And commit everything. If any of the parts rows we referenced
    -- change mid-transaction then everything gets rolled back.
 
   COMMIT;