Postgres Function dos not update

  1. 6 months ago

    Hello,

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

    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;

    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. Kem T

    6 Dec 2018 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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. Emile S

    6 Dec 2018 Europe (France, Strasbourg)

    @Paulo C id serial primary key,

    This does not look complete to me, but…

  5. Jay M

    6 Dec 2018 Pre-Release Testers, Xojo Pro NC, USA

    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?

or Sign Up to reply!