Cacher is the code snippet organizer for pro developers

$ pg_prove --ext sql --recurse --shuffle --dbname budget_test tests/database
tests/database/transaction_memos_required_when_touching_an_asset_account.sql .. 1/1
# Failed test 1: "threw P0001: "
#       caught: 0A000: cannot begin/end transactions in PL/pgSQL
#       wanted: P0001:
# Looks like you failed 1 test of 1
tests/database/transaction_memos_required_when_touching_an_asset_account.sql .. Failed 1/1 subtests

Test Summary Report
-------------------
tests/database/transaction_memos_required_when_touching_an_asset_account.sql (Wstat: 0 Tests: 1 Failed: 1)
  Failed test:  1
Files=1, Tests=1,  0 wallclock secs ( 0.04 usr  0.02 sys +  0.15 cusr  0.05 csys =  0.26 CPU)
Result: FAIL

SET client_min_messages TO 'warning';

BEGIN;
SELECT plan(1);

-- Setup
INSERT INTO accounts(account, account_kind) VALUES ('bank', 'asset'), ('cell service', 'expense');
INSERT INTO transactions(transaction_id, posted_on, booked_on) VALUES ('13b368b0-cc18-4945-9d07-8799a7eb3478', '2015-09-07', '2015-09-07');
INSERT INTO transaction_entries(transaction_id, account, amount_dt, amount_ct) VALUES
  ('13b368b0-cc18-4945-9d07-8799a7eb3478', 'cell service', 55, 0)
, ('13b368b0-cc18-4945-9d07-8799a7eb3478', 'bank', 0, 55);

SELECT throws_ok('COMMIT ', 'P0001', '');

SELECT * FROM finish();
ROLLBACK;

CREATE TABLE accounts(
    account text not null
  , account_kind text not null
  , account_id serial not null unique

  , primary key(account)
);

CREATE TABLE transactions(
    transaction_id uuid not null
  , posted_on date not null
  , booked_on date not null
  , description text

  , primary key(transaction_id)
);

CREATE TABLE transaction_entries(
    transaction_id uuid not null
  , account text not null
  , amount_dt numeric not null
  , amount_ct numeric not null
  , transaction_entry_id serial not null unique

  , primary key(transaction_id, account)
  , foreign key(transaction_id) references transactions on update cascade on delete cascade
  , foreign key(account) references accounts on update cascade on delete cascade
);

CREATE TABLE memo_accounts(
    memo_account text not null
  , memo_account_id serial not null unique
  , primary key(memo_account)
);

CREATE TABLE transaction_memos(
    transaction_id uuid not null
  , account text not null
  , memo_account text not null
  , amount_dt numeric not null
  , amount_ct numeric not null
  , transaction_memo_id serial not null unique

  , primary key(transaction_id, memo_account)
  , foreign key(transaction_id) references transactions on update cascade on delete cascade
  , foreign key(memo_account) references memo_accounts on update cascade on delete cascade
  , foreign key(account) references accounts on update cascade on delete cascade
);

-- The trigger to raise on missing transaction_memos isn't written yet