Add PostgreSQL support for CREATE TRIGGER and DROP TRIGGER

[lukaseder]

PostgreSQL doesn't support inline trigger bodies, only references to stored functions "returning a trigger," see:

• https://www.postgresql.org/docs/current/sql-createtrigger.html

For this reason, we haven't supported them yet in our DDL API, because ordinary triggers from other RDBMS can't be mapped to PostgreSQL easily.

jOOQ 3.19 now adds more support for triggers, e.g. in the form of runtime meta model / code generation support:

• Add Trigger runtime meta model #11248

This means it would be great (e.g. for integration testing), if we could support PG triggers anyway as follow:

• There's a naming convention for trigger_name -> trigger_name_function
• jOOQ controls the lifecycle of the trigger function, assuming it is never manipulated outside of jOOQ
  • Creating the trigger creates the function
    • Caveat: The function has to be re-created when the trigger doesn't exist. This could happen, for example, when the table is dropped and re-created, in case of which the trigger is also dropped, but not the trigger function!
  • Dropping the trigger drops the function

Related

• Support INSERTING, UPDATING, DELETING in PostgreSQL via TG_OP #12493

Caveats:

• Emulate REFERENCING clause in CREATE TRIGGER in dialects that don't support it natively #15354:
  • ERROR: transition table name can only be specified for an AFTER trigger
  • ERROR: transition tables cannot be specified for triggers with more than one event
  • ERROR: transition tables cannot be specified for triggers with column lists
• Cannot CREATE FUNCTION from within a Block in PostgreSQL #15373 (workaround implemented)
• Trigger bodies must RETURN NEW, instead of just RETURN
  • So, append this to the body
  • Transform all procedural logic to replace RETURN by RETURN NEW
    • Great opportunity to complete Continue work on public query object model API #14756 for procedural code

Unresolved caveats:

• Dropping a table doesn't cascade to deleting the trigger function. I'm not sure if this can be done?
  • But it means that we must always CREATE OR REPLACE the trigger function (nope, the function is created before the trigger, so CREATE OR REPLACE would succeed even if CREATE TRIGGER fails)