gistfile1.sql

-- todo: must not be able to create hold on these

CREATE TABLE dividend_issue (
        issue_id serial PRIMARY KEY,
        created timestamp DEFAULT current_timestamp,
        amount bigint CHECK (amount > 0)
);

CREATE TABLE dividend_issue_transaction (
        issue_id NOT NULL REFERENCES issued_dividends(issue_id),
        transaction_id NOT NULL REFERENCES transaction(transaction_id),
        PRIMARY KEY (issue_id, transaction_id)
);

CREATE FUNCTION issue_dividends (
        sid int,
        aid int,
        amnt bigint
) RETURNS int AS $$
DECLARE
        iid int;
        dsid security_id;
        rec record;
        tid int;
BEGIN
        dsid := (SELECT security_id FROM account WHERE account_id = aid);

        INSERT INTO dividend_issue (amount) VALUES (amnt);
        iid := currval('dividend_issue_dividend_issue_id_seq');

        FOR rec IN (SELECT user_id, balance FROM account WHERE security_id = sid AND balance > 0) LOOP
                INSERT INTO transaction (debit_account_id, credit_account_id, amount)
                VALUES (aid, user_security_account(rec.user_id, dsid), amnt * rec.balance);

                tid := currval('transaction_transaction_id_seq');

                INSERT INTO dividend_issue_transaction (issue_id, transaction_id)
                VALUES (iid, tid);
        END LOOP;

        RETURN iid;
END;