CODEWITHSUNDEEP
postgresqltriggers
Encomium
Encomium

Reputation: 277

PostgreSQL update trigger: relation 'new' does not exist

I'm trying to get a better sense of triggers and not quite understanding why I'm unable to reference the new, incoming row in this trigger function definition:

    CREATE OR REPLACE FUNCTION public.update_origin_country()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
            NEW.origin_country = a.pk 
            from reference.admin_layers_0 a 
            inner join reference.world_port_index b on a.iso_a2 = b.country 
            inner join new c on b.id = c.origin_port;
            RETURN NEW;
    END;
    $$;
    
    CREATE TRIGGER "origin_country_update" BEFORE INSERT OR UPDATE OF "origin_port" ON "active"."events"
    FOR EACH ROW
    EXECUTE PROCEDURE "public"."update_origin_country"();

When I update the field origin_ports with the trigger applied, I get the error:

Relation "new" does not exist.

Not sure how to get around it. The goal is to evaluate the new row coming in, checking for the value in origin_ports and using that to update the value for origin_country from a query referencing a port table and a country name table. Any help appreciated.

Upvotes: 0

Views: 946

Answers (1)

melcher
melcher

Reputation: 1601

I don't totally understand the desired logic but instead of joining to new (which is invalid since the new row is not a relation/table) you can just add the filter to a where clause, something like:

 CREATE OR REPLACE FUNCTION public.update_origin_country()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
            NEW.origin_country = a.pk 
            from reference.admin_layers_0 a 
            inner join reference.world_port_index b on a.iso_a2 = b.country 
            WHERE new.origin_port = b.id;
            RETURN NEW;
    END;
    $$;
    
    CREATE TRIGGER "origin_country_update" BEFORE INSERT OR UPDATE OF "origin_port" ON "active"."events"
    FOR EACH ROW
    EXECUTE PROCEDURE "public"."update_origin_country"();

Is that what you want it to do?

Upvotes: 3

Related Questions