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