New feature: generate trigger functions

[mattiarossi]

Hi, just started experimenting using plv8ify for one of our projects, and so far we're loving it.
Our Postgres data model is json heavy, and I am starting to struggle handling properly (as in, with types and not having to build json objects manually every time) backed heavy stored procedures.
I think plv8ify would be a perfect fit for our use case, as. it would allow us to use typing to write code and at the same time depoy such code in the database.

I think I could work on a PR to add the capability of detecting trigger functions and handling the proper PLV8 code generation

E.G., I'd like to be able to define a trigger function plv8ts style:

export type MyTableRow = {
  // Either JS or plv8 types can be used here
  id: number;
  event_name: string;
  event_date_time: Date;
  event_location: text;
  attendee_limit: integer;
};

export function sample_trigger(
  NEW: MyTableRow,
  OLD: MyTableRow,
): trigger<MyTableRow> {
  plv8.elog(NOTICE, 'NEW = ', JSON.stringify(NEW));
  plv8.elog(NOTICE, 'OLD = ', JSON.stringify(OLD));
  plv8.elog(NOTICE, 'TG_OP = ', TG_OP);
  plv8.elog(NOTICE, 'TG_ARGV = ', TG_ARGV[0]);
  if (TG_OP === 'UPDATE') {
    NEW.id = 102;
    return NEW;
  }
}

and have typescript support for the NEW and OLD object, and at the same time be able to auto generate the trigger function with the proper syntax.

The only challenge I see is establishing a proper pattern for identifying a trigger function: ts-morph properly detects the custom type, but it returns something like this in the returnType field:

{
  name: 's_trigger',
  isExported: true,
  parameters: [
    {
      name: 'NEW',
      type: 'import("/Users/.../plv8/src/test/trigger/trigger").MyTableRow'
    },
    {
      name: 'OLD',
      type: 'import("/Users/.../src/test/trigger/trigger").MyTableRow'
    }
  ],
  comments: [ '// trigger' ],
  returnType: 'import("/Users/.../plv8/src/test/trigger/trigger").trigger<import("/Users/.../plv8/src/test/trigger/trigger").MyTableRow>'
}

The easiest path for identifying a trigger function would be to 'decorate' the function with a comment that includes a specific keyword like 'plv8_trigger' and handling things from there.
I feel like adding a generic rule to determine whether a function is a trigger or not would be tricky and prone to errors (other than forcing the return type to have a fixed keyword in the name)

e.g. something like:

{
  name: 's_trigger',
  isExported: true,
  parameters: [
    {
      name: 'NEW',
      type: 'import("/Users/.../plv8/src/test/trigger/trigger").MyTableRow'
    },
    {
      name: 'OLD',
      type: 'import("/Users/.../plv8/src/test/trigger/trigger").MyTableRow'
    }
  ],
  comments: [ '// plv8_trigger' ],
  returnType: 'TRIGGER'
}

would then be easy to catch and handle in the getPLV8SQLFunction function to properly strip the input params

I do have a local prototype working for this functionality, just let me know if that would be an acceptable feature to be added

[divyenduz]

Hey thanks for the detailed comment, I do not fully understand some things and have some follow up questions

1. What is the difference between a trigger function and a normal function? If I understand correctly, you want to correctly resolve object types in function parameters (currently it falls back to JSON), right? Or is there more to it?
2. Yes, decoration is the right way to go, if we must detect/mark a function separately. If you see the readme for --volatility, we already use decorators for changing function volatility.

I do have a local prototype working for this functionality, just let me know if that would be an acceptable feature to be added
Absolutely, thanks for the contribution. Please feel free to create a PR

[mattiarossi]

1. What is the difference between a trigger function and a normal function? If I understand correctly, you want to correctly resolve object types in function parameters (currently it falls back to JSON), right? Or is there more to it?

A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers). Special local variables named TG_something are automatically defined to describe the condition that triggered the call.

i.e these variables are automatically provided by the global context

declare var TG_NAME: string;
declare var TG_WHEN: string;
declare var TG_LEVEL: string;
declare var TG_OP: string;
declare var TG_RELID: number;
declare var TG_TABLE_NAME: string;
declare var TG_TABLE_SCHEMA: string;
declare var TG_ARGV: string[];

and also:

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW record 
new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

OLD record 
old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

In short, a trigger function gets passed special vars that change depending wheter the trigger is a before/after insert and whether it is executed for each row
The common rule is that the function has no parameters, and needs to detect in which mode the trigger has been defined
If the trigger is run for each row, then depending on whether it is an insert,update or delete the special variables OLD and NEW will be populated, and will hold a single record of that table

In plv8ify, a trigger function should return a specific type (TRIGGER) and remove all parameters from the function
In typescript, OLD and NEW need to be defined as function parameters in order to get the type checking and transpile correctly

[mattiarossi]

2. Yes, decoration is the right way to go, if we must detect/mark a function separately. If you see the readme for --volatility, we already use decorators for changing function volatility.

Got it, thanks

[divyenduz]

Sounds good! Thanks for the explanation, feel free to raise a PR, we can surely use such a feature!

[mattiarossi]

Closing, live in 0.0.53