Hi everyone, I've adapted the work that Konstantina did for pl/julia as part of her GSOC project to add an example of handling triggers to plsample. Which was based from pl/tcl and pl/perl.
One aspect that I'm not sure about is whether the example should be duplicating code (as it is now) for keeping an example contained within a single function. The only reason I can come up with is to try to read through an example with minimal jumping around. Hoping this is a good start. Regards, Mark
diff --git a/src/test/modules/plsample/expected/plsample.out b/src/test/modules/plsample/expected/plsample.out index a0c318b6df..832db79b5c 100644 --- a/src/test/modules/plsample/expected/plsample.out +++ b/src/test/modules/plsample/expected/plsample.out @@ -34,3 +34,280 @@ NOTICE: argument: 0; name: a1; value: {foo,bar,hoge} (1 row) +CREATE FUNCTION my_trigger_func() RETURNS trigger AS $$ +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end +$$ language plsample; +CREATE TABLE my_table (num integer, description text); +CREATE TRIGGER my_trigger_func BEFORE INSERT OR UPDATE ON my_table + FOR EACH ROW EXECUTE FUNCTION my_trigger_func(); +CREATE TRIGGER my_trigger_func2 AFTER INSERT OR UPDATE ON my_table + FOR EACH ROW EXECUTE FUNCTION my_trigger_func(8); +INSERT INTO my_table (num, description) +VALUES (1, 'first'), (2, 'second'), (1, 'first'); +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered BEFORE +NOTICE: triggered per row +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered BEFORE +NOTICE: triggered per row +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered BEFORE +NOTICE: triggered per row +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func2 +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered AFTER +NOTICE: triggered per row +NOTICE: trigger arg[0]: 8 +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func2 +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered AFTER +NOTICE: triggered per row +NOTICE: trigger arg[0]: 8 +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func2 +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by INSERT +NOTICE: triggered AFTER +NOTICE: triggered per row +NOTICE: trigger arg[0]: 8 +NOTICE: out +UPDATE my_table +SET description = 'first, modified once' +WHERE num = 1; +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by UPDATE +NOTICE: triggered BEFORE +NOTICE: triggered per row +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by UPDATE +NOTICE: triggered BEFORE +NOTICE: triggered per row +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func2 +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by UPDATE +NOTICE: triggered AFTER +NOTICE: triggered per row +NOTICE: trigger arg[0]: 8 +NOTICE: out +NOTICE: source text of function "my_trigger_func": +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: compile trigger function: my_trigger_func(TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args) +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end + +NOTICE: trigger name: my_trigger_func2 +NOTICE: trigger relation: my_table +NOTICE: trigger relation schema: public +NOTICE: triggered by UPDATE +NOTICE: triggered AFTER +NOTICE: triggered per row +NOTICE: trigger arg[0]: 8 +NOTICE: out +DROP TRIGGER my_trigger_func ON my_table; +DROP TRIGGER my_trigger_func2 ON my_table; +DROP FUNCTION my_trigger_func; diff --git a/src/test/modules/plsample/plsample.c b/src/test/modules/plsample/plsample.c index 6fc33c728c..a2c89d9ab2 100644 --- a/src/test/modules/plsample/plsample.c +++ b/src/test/modules/plsample/plsample.c @@ -19,6 +19,7 @@ #include "catalog/pg_type.h" #include "commands/event_trigger.h" #include "commands/trigger.h" +#include "executor/spi.h" #include "funcapi.h" #include "utils/builtins.h" #include "utils/lsyscache.h" @@ -29,6 +30,7 @@ PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(plsample_call_handler); static Datum plsample_func_handler(PG_FUNCTION_ARGS); +static HeapTuple plsample_trigger_handler(PG_FUNCTION_ARGS); /* * Handle function, procedure, and trigger calls. @@ -51,6 +53,8 @@ plsample_call_handler(PG_FUNCTION_ARGS) * (TriggerData *) fcinfo->context includes the information of the * context. */ + retval = PointerGetDatum(plsample_trigger_handler(fcinfo)); + ereport(NOTICE, (errmsg("out"))); } else if (CALLED_AS_EVENT_TRIGGER(fcinfo)) { @@ -185,3 +189,177 @@ plsample_func_handler(PG_FUNCTION_ARGS) ret = InputFunctionCall(&result_in_func, source, result_typioparam, -1); PG_RETURN_DATUM(ret); } + +/* + * plsample_trigger_handler + * + * Function called by the call handler for trigger execution. + */ +static HeapTuple +plsample_trigger_handler(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + char *string; + volatile HeapTuple rettup; + HeapTuple pl_tuple; + Datum ret; + char *source; + bool isnull; + Form_pg_proc pl_struct; + char *proname; + int rc PG_USED_FOR_ASSERTS_ONLY; + MemoryContext proc_cxt = NULL; + MemoryContext old_cxt = NULL; + char *trigger_code = NULL; + + /* Make sure this is being called from a trigger. */ + if (!CALLED_AS_TRIGGER(fcinfo)) + elog(ERROR, "not called by trigger manager"); + + /* Connect to the SPI manager */ + if (SPI_connect() != SPI_OK_CONNECT) + elog(ERROR, "could not connect to SPI manager"); + + rc = SPI_register_trigger_data(trigdata); + Assert(rc >= 0); + + /* Fetch the source text of the function. */ + pl_tuple = SearchSysCache(PROCOID, + ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 0, 0, 0); + if (!HeapTupleIsValid(pl_tuple)) + elog(ERROR, "cache lookup failed for function %u", + fcinfo->flinfo->fn_oid); + + /* + * Extract and print the source text of the function. This can be used as + * a base for the function validation and execution. + */ + pl_struct = (Form_pg_proc) GETSTRUCT(pl_tuple); + proname = pstrdup(NameStr(pl_struct->proname)); + ret = SysCacheGetAttr(PROCOID, pl_tuple, Anum_pg_proc_prosrc, &isnull); + if (isnull) + elog(ERROR, "could not find source text of function \"%s\"", + proname); + source = DatumGetCString(DirectFunctionCall1(textout, ret)); + ereport(NOTICE, + (errmsg("source text of function \"%s\": %s", + proname, source))); + + PG_TRY(); + { + char *internal_args = "TD_name, TD_relid, TD_table_name, TD_table_schema, TD_event, TD_when, TD_level, TD_NEW, TD_OLD, args"; + + /* + * The code generated for this trigger is: proname(internal_args) + * source + */ + int length = + strlen(proname) + strlen(source) + strlen(internal_args) + 4; + + /* + * Allocate a context that will hold all the Postgres data for the + * procedure. + */ + proc_cxt = AllocSetContextCreate(TopMemoryContext, + "PL/Sample function", ALLOCSET_SMALL_SIZES); + + trigger_code = (char *) palloc0(length * sizeof(char)); + trigger_code[0] = '\0'; + strcpy(trigger_code, proname); + strcat(trigger_code, "("); + strcat(trigger_code, internal_args); + strcat(trigger_code, ") "); + strcat(trigger_code, source); + + ereport(NOTICE, + (errmsg("compile trigger function: %s", trigger_code))); + + old_cxt = MemoryContextSwitchTo(proc_cxt); + + MemoryContextSwitchTo(old_cxt); + } + PG_CATCH(); + { + PG_RE_THROW(); + } + PG_END_TRY(); + + pfree(source); + + ReleaseSysCache(pl_tuple); + + PG_TRY(); + { + int i; + + ereport(NOTICE, + (errmsg("trigger name: %s", trigdata->tg_trigger->tgname))); + string = SPI_getrelname(trigdata->tg_relation); + ereport(NOTICE, (errmsg("trigger relation: %s", string))); + pfree(string); + + string = SPI_getnspname(trigdata->tg_relation); + ereport(NOTICE, (errmsg("trigger relation schema: %s", string))); + pfree(string); + + /* Example handling of different trigger aspects. */ + + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + { + ereport(NOTICE, (errmsg("triggered by INSERT"))); + rettup = trigdata->tg_trigtuple; + } + else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) + { + ereport(NOTICE, (errmsg("triggered by DELETE"))); + rettup = trigdata->tg_trigtuple; + } + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + { + ereport(NOTICE, (errmsg("triggered by UPDATE"))); + rettup = trigdata->tg_trigtuple; + } + else if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event)) + { + ereport(NOTICE, (errmsg("triggered by TRUNCATE"))); + rettup = trigdata->tg_trigtuple; + } + else + elog(ERROR, "unrecognized event: %u", trigdata->tg_event); + + if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) + ereport(NOTICE, (errmsg("triggered BEFORE"))); + else if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) + ereport(NOTICE, (errmsg("triggered AFTER"))); + else if (TRIGGER_FIRED_INSTEAD(trigdata->tg_event)) + ereport(NOTICE, (errmsg("triggered INSTEAD OF"))); + else + elog(ERROR, "unrecognized when: %u", trigdata->tg_event); + + if (TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + ereport(NOTICE, (errmsg("triggered per row"))); + else if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + ereport(NOTICE, (errmsg("triggered per statement"))); + else + elog(ERROR, "unrecognized level: %u", trigdata->tg_event); + + /* + * Iterate through all of the trigger arguments, printing each input + * value. + */ + for (i = 0; i < trigdata->tg_trigger->tgnargs; i++) + ereport(NOTICE, + (errmsg("trigger arg[%i]: %s", i, + trigdata->tg_trigger->tgargs[i]))); + } + PG_CATCH(); + { + PG_RE_THROW(); + } + PG_END_TRY(); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish() failed"); + + return rettup; +} diff --git a/src/test/modules/plsample/sql/plsample.sql b/src/test/modules/plsample/sql/plsample.sql index bf0fddac7f..e3ffc4ecf9 100644 --- a/src/test/modules/plsample/sql/plsample.sql +++ b/src/test/modules/plsample/sql/plsample.sql @@ -13,3 +13,31 @@ AS $$ Example of source with void result. $$ LANGUAGE plsample; SELECT plsample_result_void('{foo, bar, hoge}'); + +CREATE FUNCTION my_trigger_func() RETURNS trigger AS $$ +if TD_event == "INSERT" + return TD_NEW +elseif TD_event == "UPDATE" + return TD_NEW +else + return "OK" +end +$$ language plsample; + +CREATE TABLE my_table (num integer, description text); +CREATE TRIGGER my_trigger_func BEFORE INSERT OR UPDATE ON my_table + FOR EACH ROW EXECUTE FUNCTION my_trigger_func(); +CREATE TRIGGER my_trigger_func2 AFTER INSERT OR UPDATE ON my_table + FOR EACH ROW EXECUTE FUNCTION my_trigger_func(8); + +INSERT INTO my_table (num, description) +VALUES (1, 'first'), (2, 'second'), (1, 'first'); + +UPDATE my_table +SET description = 'first, modified once' +WHERE num = 1; + +DROP TRIGGER my_trigger_func ON my_table; +DROP TRIGGER my_trigger_func2 ON my_table; +DROP FUNCTION my_trigger_func; +