Using newly inserted values in trigger function in PostgreSQL

Question

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

Answer 1

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.

Answer 2

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

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

Hope that helps.