Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- CREATE EXTENSION "uuid-ossp";
CREATE FUNCTION uuid_generate_v1() RETURNS uuid LANGUAGE SQL AS $$SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring);$$;

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();

ERROR:  syntax error at or near "'balance'"
LINE 5:             SUM(amount) AS '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 3 at EXECUTE