Postgres Function dos not update

Databases
1

Hello,

I’m implementing a small postgree database with a function to get a consumption average. The SQL is:

[code]Create table veiculos (
id serial primary key,
id_empresas integer references empresas(id) on delete restrict,
km_atual integer,
custo_km numeric(12,2),
);

CREATE OR REPLACE FUNCTION fn_update_veiculos()
RETURNS trigger AS $BODY$

declare
custo_medio decimal(12,2):=0;
BEGIN
SELECT round(COALESCE(SUM(custo),0) / MAX(km_atual),2) FROM veiculos_despesas WHERE veiculos_despesas.id_veiculo=NEW.id into custo_medio;
UPDATE veiculos set custo_km=(custo_medio) where veiculos.id=new.id_veiculo;

RETURN NEW;

END;
$BODY$ LANGUAGE plpgsql;[/code]

The problem is in the line UPDATE veiculos set custo_km=(custo_medio) where veiculos.id=new.id_veiculo;

field custo_km every return NULL (and custo_medio is = 2.51).
could someone help-me with this off topic?

2

After you create the trigger function, as you have, you have to assign it to the table. See the PostgreSQL documentation for CREATE TRIGGER.

3

Sorry, but I have the triiger created. but i dont published it.

CREATE TRIGGER trigger_update_veiculos AFTER INSERT OR UPDATE OR DELETE ON veiculos_despesas FOR EACH ROW EXECUTE PROCEDURE fn_update_veiculos();

the problem is inside fn_update_veiculos

4

This does not look complete to me, but…

5

I assume the Create Table statement is just to give us the table details, but you aren’t running that statement yourself as you already have the table? Because that statement has an error - a trailing comma after the last column. Maybe you could post the full table definition as it exists on your system.

Also, have you tried running the update statement manually, outside of the function?