I’ve tried several variation and looked at examples of existing threads related to running triggers on foreign table and none have worked so far. The postgres_fdw documentation suggests setting the SHEMA path, which I’ve done (although I’m not sure if this is correct).

Here is a simple procedure to update a local db table whenever a change has taken place on the foreign table;

CREATE OR REPLACE FUNCTION public.log_budget2_updates()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
    SET search_path=public
AS $BODY$
BEGIN
    -- Insert a log entry with the word "It works" 
    INSERT INTO public.log_table (log)
    VALUES ('It_works!');
    RETURN NEW; -- Return the updated row
END;
$BODY$;

ALTER FUNCTION public.log_budget2_updates()
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.log_budget2_updates() TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.log_budget2_updates() TO postgres;


-- Trigger:
CREATE OR REPLACE TRIGGER test_all_changes
    AFTER INSERT OR DELETE OR UPDATE 
    ON public.budget3
    FOR EACH ROW
    EXECUTE FUNCTION public.log_budget2_updates();

I’m using Postgres16

I am expecting that the public.log_table is updated with the words “It_works!”.

I’ve tested this using a separate local test table and the same procedure and it works as expected.