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

0

Mine has been giving a mistake that I can not understand talking about having a bug in $, but when I used another example procedure from the internet almost in the same way as mine did, then the error is being in how to do it procedure.

The operation table has some fields, operation id pk, float value, varchar description, type char (1), account_id fk. And the account table has the fields id_acta pk, float balance, name. I want the procedure to update the balance of the account table when there is some modification in the operation table.

Here's the code I'm trying to use to do the procedure:

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 operation     for each row execute update procedure (type, value);

ERROR: trigger functions can not 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 "refresh" near line 1 SQL state: 42P13

Thanks in advance for the help.

Fixed:

Resolution code:

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_operation AFTER INSERT OR UPDATE OR DELETE ON operation FOR EACH ROW EXECUTE PROCEDURE update ();

Insert into operation (description, value, type, category_id, account_id) values ('Test', 100, 'S', 1, 1);

    
asked by anonymous 22.11.2018 / 23:13

1 answer

0

In postgres to use conditional control of code flow, IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF is used as per documentation.

link

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;
    
23.11.2018 / 00:17