3

I have this table:

CREATE TABLE accounts (
  id BIGINT NOT NULL UNIQUE,
  balance INTEGER DEFAULT 0
);

And I need to update each user's balance after inserting rows to 2 different tables, transfers and deposits.

I've created 2 functions to calculate new user balances and updating it:

/* function for getting user balance */
CREATE FUNCTION get_balance(bigint) RETURNS bigint
    AS 'SELECT (
  SELECT COALESCE(sum(CASE WHEN won THEN amount * (multiplier - 1) ELSE -amount END), 0)
  FROM transfers
  WHERE user_id = $1
) + (
  SELECT COALESCE(sum(amount), 0)
  FROM deposits
  WHERE user_id = $1
) as sum;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

/* function for updating user balance */
CREATE FUNCTION upsert_balance(bigint) RETURNS VOID
    AS 'INSERT INTO ACCOUNTS (id, balance)
    VALUES ($1, get_balance($1))
  ON CONFLICT (id) DO
  UPDATE SET balance = get_balance($1);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

But I'm not sure how to use the newly inserted values in a trigger callback, because I need to pass the id as an argument to the upsert_balance function.

How can I accomplish this?

EDIT: I fixed it, thanks to Adam's answer, here is the working version in case anyone needs it:

CREATE FUNCTION upsert_balance() RETURNS trigger AS $trigger_bound$
BEGIN
    INSERT INTO ACCOUNTS (id, balance)
    VALUES (NEW.user_id, get_balance(NEW.user_id))
    ON CONFLICT (id) DO
    UPDATE SET balance = get_balance(NEW.user_id);

    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER update_balance_on_inserting_transfer
AFTER INSERT OR UPDATE ON transfers
FOR EACH ROW
EXECUTE PROCEDURE upsert_balance();
2

Within the trigger function you can use NEW.column_name to refer to the newly inserted / updated value. In the reverse, OLD.column_name will refer to the value prior to the update / delete.

More info here: https://www.postgresql.org/docs/current/static/sql-createtrigger.html

Hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.