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$
;

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$
;

• sql
• postgresql
• kotlin
• jooq

• Could you look for the function with select prosrc from pg_proc where prosrc like '%other_schema%';? – Guillaume Outters Commented Mar 5 at 16:00
• @GuillaumeOutters thanks for your comment, I found the method via the information_schema in the mean time ;-) See my edit above. – Candlejack Commented Mar 5 at 16:04
• Well, that EXECUTE PROCEDURE doesn't look like correct SQL. – Laurenz Albe Commented Mar 5 at 19:48
• If I use EXECUTE PROCEDURE directly in the trigger code it works just fine. So it seems the issue is that for some reason the schema can not be resolved or sth like that. – Candlejack Commented Mar 6 at 7:41
• @LukasEder The actual question is, why this trigger is not working correctly :-) Any pointers on this? – Candlejack Commented Mar 6 at 7:48

1 Answer 1

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:

• https://www.jooq./doc/latest/manual/sql-building/procedural-statements/procedural-call/
• https://www.jooq./doc/latest/manual/sql-building/procedural-statements/procedural-execute/

In other words, do this:

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