jooq trigger creating invalid function in postgres db

I try to create a trigger on one of my tables via jooq, the trigger does get created and also fires when inserting data into the table but fails with a syntax error.

The trigger is created like this:

dsl.createTrigger("my_trigger")
  .beforeInsert().orUpdate().on(MY_TABLE).forEachRow()
  .`as`(DSL.execute("EXECUTE PROCEDURE other_schema.update_method()")).execute()

MY_TABLE was created via jooq and already exists in the database. update_method also exists in other_schema.

The created trigger code looks like this:

create trigger my_trigger before insert or update on MY_TABLE for each row execute function my_trigger_function()

On inserting or updating rows in the table I get this error:

ERROR: syntax error at or near "other_schema"

So it seems my initial code has been wrapped in a function by jooq but that obviously does not work correctly.

If I change the trigger code to directly call other_schema.update_method everything works as expected.

I have no idea where my_trigger_function is created (database is postgre 13) so I do not know what the actual code looks like. Neither do I see a way to just use the code I provided.

What am I missing here?

Update:

I found the generated wrapper function in the public schema (d'oh)

CREATE OR REPLACE FUNCTION public.my_trigger_function()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
  execute 'EXECUTE PROCEDURE other_schema.update_method()';
  return new;
end;
$function$
;

Still the question remains why that trigger is not executing correctly. It seems that the use of the schema qualifier is some kind of issue.

Update:

Using call instead of exeucte results in the method not being found. I guess this is related to the definition of the method which looks like this:

CREATE OR REPLACE FUNCTION other_schema.update_method()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    new.tsv_search_fulltext :=
            setweight(to_tsvector(coalesce(new.search_fulltext,'')), 'A') ;
    return new;
end
$function$
;

Solution

The EXECUTE PROCEDURE syntax in PostgreSQL is reserved for CREATE TRIGGER statements only:

CREATE [ ... ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ ... ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

jOOQ already does this for you. The function_name above is generated by jOOQ, containing your trigger body (as would be done in all other RDBMS).

There's no EXECUTE PROCEDURE syntax in PL/pgSQL, use CALL instead. EXECUTE is for interpreting SQL strings and running them, see the following sections in the jOOQ manual for more details:

In other words, do this:

dsl.createTrigger("my_trigger")
  .beforeInsert().orUpdate().on(MY_TABLE).forEachRow()
  .`as`(DSL.call("other_schema.update_method"))
  .execute()