How can I make a trial to update the balance in postgres?

Question

Mine has been giving an error that I can not understand talking q is wrong in $, but when I used another example internet trial almost in the same way as mine worked, then the mistake is being on how to make the trial.

The table operation has some fields, id_pk operation, float value, varchar description, char(1), id_conta fk. And the account table has the pk id_account field, float balance, name. I want the process to update the balance of the table account when there is some change in the operation table.

Follow the code I’m trying to use to make the process:

CREATE OR REPLACE FUNCTION atualizasaldo(tipo char, valor float) RETURNS trigger AS $$
BEGIN
    if (tipo = 'E') then
        update conta 
        set saldo = saldo + valor
        WHERE id_conta = OLD.id_conta;
    elsif (tipo = 'S') then
        update conta 
        set saldo = saldo - valor
        WHERE id_conta = OLD.id_conta;
    end if;
END
$$ LANGUAGE PLPGSQL;

create trigger operacao_trigger after insert or update or delete on operacao 
    for each row execute procedure atualizasaldo(tipo, valor);

ERROR:  trigger functions cannot have declared arguments
HINT:  The arguments of the trigger can be accessed through TG_NARGS and TG_ARGV instead.
CONTEXT:  compilation of PL/pgSQL function "atualizasaldo" near line 1
SQL state: 42P13

Thanks for your help.

Solved:

Code of the resolution:

CREATE OR REPLACE FUNCTION atualizasaldo() RETURNS trigger AS $$
DECLARE
    tipo char;
    valor float;
BEGIN
    if(TG_OP = 'DELETE') then
        tipo = OLD.tipo;
        valor = OLD.valor;
        if (tipo = 'E') then
            update conta 
            set saldo = saldo - valor
            WHERE id_conta = OLD.id_conta;
        elsif (tipo = 'S') then
            update conta 
            set saldo = saldo + valor
            WHERE id_conta = OLD.id_conta;
        end if;
        RETURN NULL;
    elsif(TG_OP = 'UPDATE') then
        tipo = NEW.tipo;
        valor = NEW.valor;
        if (tipo = 'E') then
            update conta 
            set saldo = saldo + valor
            WHERE id_conta = OLD.id_conta;
        elsif (tipo = 'S') then
            update conta 
            set saldo = saldo - valor
            WHERE id_conta = OLD.id_conta;
        end if;
        RETURN NULL;
    elsif(TG_OP = 'INSERT') then
        tipo = NEW.tipo;
        valor = NEW.valor;
        if (tipo = 'E') then
            update conta 
            set saldo = saldo + valor
            WHERE id_conta = NEW.id_conta;
        elsif (tipo = 'S') then
            update conta 
            set saldo = saldo - valor
            WHERE id_conta = NEW.id_conta;
        end if;
        RETURN NULL;
    end if;

END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER tg_operacao AFTER INSERT OR UPDATE OR DELETE ON operacao 
FOR EACH ROW EXECUTE PROCEDURE atualizasaldo();

Insert into operacao(descricao, valor, tipo, id_categoria, id_conta) values('Teste', 100, 'S', 1, 1);

Answer 1

In postgres to use conditional control of the code flow, the IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF, as documented.

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

CREATE OR REPLACE FUNCTION atualizasaldo() RETURNS trigger AS $$
BEGIN
    if (TG_OP = 'DELETE') then
        update conta 
        set saldo = (select sum(valor) from operacao where id_operacao = OLD.id_operacao) 
        WHERE id_conta = OLD.id_conta;
    elsif (TG_OP = 'UPDATE') then
        update conta 
        set saldo = (select sum(valor) from operacao where id_operacao = OLD.id_operacao) 
        WHERE id_conta = OLD.id_conta;
    elsif (TG_OP = 'INSERT') then
        update conta 
        set saldo = (select sum(valor) from operacao where id_operacao = NEW.id_operacao) 
        WHERE id_conta = NEW.id_conta;
    end if;
END
$$ LANGUAGE PLPGSQL;