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