I'm having an issue with this code when I try to input values into the transactions table

Question

So I'm setting up a schema in which I can input transactions of a journal entry independent of each other but also that rely on each other (mainly to ensure that debits = credits). I set up the tables, function, and trigger. Then, when I try to input values into the transactions table, I get the error below. I'm doing all of this in pgAdmin4.

CREATE TABLE transactions (
    transactions_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    entry_id INTEGER NOT NULL,
    post_date DATE NOT NULL,
    account_id INTEGER NOT NULL,
    contact_id INTEGER NULL,
    description TEXT NOT NULL,
    reference_id UUID NULL,
    document_id UUID NULL,
    amount NUMERIC(12,2) NOT NULL
);

CREATE TABLE entries (
    id UUID PRIMARY KEY,
    test_date DATE NOT NULL,
    balance NUMERIC(12,2)
    CHECK (balance = 0.00)
);

CREATE OR REPLACE FUNCTION transactions_biut()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
        EXECUTE 'INSERT INTO entries (id,test_date,balance)
        SELECT
            entry_id,
            post_date,
            SUM(amount) AS ''balance''
        FROM
            transactions
        GROUP BY
            entry_id;';
    END;
    $$;

CREATE TRIGGER transactions_biut
    BEFORE INSERT OR UPDATE ON transactions
FOR EACH ROW EXECUTE PROCEDURE transactions_biut();

INSERT INTO transactions (
    entry_id,
    post_date,
    account_id,
    description,
    amount
)
VALUES
    (
        '1',
        '2019-10-01',
        '101',
        'MISC DEBIT: PAID FOR FACEBOOK ADS',
        -200.00
    ),
    (
        '1',
        '2019-10-01',
        '505',
        'MISC DEBIT: PAID FOR FACEBOOK ADS',
        200.00
    );

After I execute this input, I get the following error:

ERROR:  column "id" of relation "entries" does not exist
LINE 1: INSERT INTO entries (id,test_date,balance)
                             ^
QUERY:  INSERT INTO entries (id,test_date,balance)
        SELECT
            entry_id,
            post_date,
            SUM(amount) AS "balance"
        FROM
            transactions
        GROUP BY
            entry_id;
CONTEXT:  PL/pgSQL function transactions_biut() line 2 at EXECUTE
SQL state: 42703

[I cannot reproduce](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=395cace63e4135f69e5ddf5f07ed143b). The syntax error I get is about `balance`. — Bergi, Oct 11 '19 at 22:11
Btw, [no need to use `EXECUTE`](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ab7b43a4bcef742b1efb19fea13c44ba) with a string query. Just do the `INSERT` as a plsql statement. — Bergi, Oct 11 '19 at 22:14
@Bergi thank you for the input, still learning my way around it all. — James Agius, Oct 12 '19 at 00:07

score 1 · Accepted Answer · answered Oct 11 '19 at 22:10

1

There are a few problems here:

You're not returning anything from the trigger function => should probably be return NEW or return OLD since you're not modifying anything
Since you're executing the trigger before each row, it's bound to fail for any transaction that isn't 0 => maybe you want a deferred constraint trigger?
You're not grouping by post_date, so your select should fail
You've defined entry_id as INTEGER, but entries.id is of type UUID

Also note that this isn't really going to scale (you're summing up all transactions of all days, so this will get slower and slower...)

answered Oct 11 '19 at 22:10

Chris

312
1
11

Thank you Chris. I was actually consulting with a friend on how to limit the trigger to just the transactions I am currently trying to input. For your second point, is there a way of how to apply it to the entire statement rather than each row? – James Agius Oct 12 '19 at 00:25
Have a look at this question: https://stackoverflow.com/q/16323236/7159043 for deferred constraints. – Chris Oct 12 '19 at 06:42
Generally, you may want to rethink your model, e.g. add an intermediate table with transaction "groups", which you "move" to your transaction table only if they add up to 0. – Chris Oct 12 '19 at 06:45

score 0 · Answer 2 · answered Oct 14 '19 at 17:42

@chirs I was able to figure out how to create a functioning solution using statement-level triggers:

CREATE TABLE transactions (
    transactions_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    entry_id INTEGER NOT NULL,
    post_date DATE NOT NULL,
    account_id INTEGER NOT NULL,
    contact_id INTEGER NULL,
    description TEXT NOT NULL,
    reference_id UUID NULL,
    document_id UUID NULL,
    amount NUMERIC(12,2) NOT NULL
);

CREATE TABLE entries (
    entry_id INTEGER PRIMARY KEY,
    post_date DATE NOT NULL,
    balance NUMERIC(12,2),
    CHECK (balance = 0.00)
);

CREATE OR REPLACE FUNCTION transactions_entries() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO entries
                SELECT o.entry_id, o.post_date, SUM(o.amount) FROM old_table o GROUP BY o.entry_id, o.post_date;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO entries
                SELECT o.entry_id, n.post_date, SUM(n.amount) FROM new_table n, old_table o GROUP BY o.entry_id, n.post_date;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO entries
                SELECT n.entry_id,n.post_date, SUM(n.amount) FROM new_table n GROUP BY n.entry_id, n.post_date;
        END IF;

        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER transactions_ins
    AFTER INSERT ON transactions
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE PROCEDURE transactions_entries();
CREATE TRIGGER transactions_upd
    AFTER UPDATE ON transactions
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE PROCEDURE transactions_entries();
CREATE TRIGGER transactions_del
    AFTER DELETE ON transactions
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE PROCEDURE transactions_entries();

Any thoughts on optimization?

Are you sure you want to do a *cartesian join* between the new and old rows on update? That seems strange to me. Also, on delete in `transactions`, you duplicate the according rows in `entries` - why? What is meant to happen if the original "entry" had e.g. 4 "transactions" and only two of them are deleted (assuming two each add up to 0)? — Chris, Oct 14 '19 at 19:26
I think one thing to do is test with some not-so-simple cases to see what happens and if that corresponds to what you're looking for. — Chris, Oct 14 '19 at 19:27
Hmm, that's food for thought. I will check that out and start running different complex entries and see what happens. Thank you Chris! — James Agius, Oct 16 '19 at 13:23

I'm having an issue with this code when I try to input values into the transactions table

2 Answers2