[moving to -hackers with BC to -general]
Dimitri Fontaine <dimitri@xxxxxxxxxxxxxx> wrote:
> "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes:
>
>> PL/pgSQL seems tantalizingly close to being useful for developing
>> a generalized trigger function for notifying the client of
>> changes. I don't know whether I'm missing something or whether
>> we're missing a potentially useful feature here. Does anyone see
>> how to fill in where the commented question is, or do I need to
>> write this function in C?
>
> See those:
>
> http://tapoueh.org/articles/blog/_Dynamic_Triggers_in_PLpgSQL.html
>
http://www.pgsql.cz/index.php/PL_toolbox_%28en%29#Record.27s_functions
>
>> for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop
>> select quote_ident(attname) from pg_catalog.pg_attribute
>> where attrelid = tg_relid and attnum = keycols[i]::oid
>
> Beware of attisdropped, which I've not fixed in the published URL
> before (the tapoueh.org one).
Thanks.
In the absence of an earlier response, though, I went ahead and
wrote the attached, which has passed some initial programmer testing
and is scheduled to start business analyst testing tomorrow with the
application software for production deployment in a couple months.
We probably won't go back to PL/pgSQL for this now.
I'm assuming that while I have an AccessShareLock on the index
relation for the primary key, any attributes it tells me are used by
that relation will not have the attisdropped flag set?
What this trigger function does is to issue a NOTIFY to the channel
specified as a parameter to the function in CREATE TRIGGER (with
'tcn' as the default), and a payload consisting of the table name, a
code for the operation (Insert, Update, or Delete), and the primary
key values. So, an update to a Party record for us might generate
this NOTIFY payload:
"Party",U,"countyNo"='71',"caseNo"='2011CF001234',"partyNo"='1'
This is one of those things which our shop needs, but I was planning
to post it for the first 9.2 CF fest to see if anyone else was
interested. It struck me while typing this post that for general
use the schema would probably need to be in there, but I'll worry
about that later, if anyone else *is* interested. If anyone wants
it I can provide Java code to tear apart the NOTIFY payloads using
the Pattern and Matches classes.
I'll add to the first 9.2 CF referencing this post.
-Kevin
*** a/src/backend/utils/adt/trigfuncs.c
--- b/src/backend/utils/adt/trigfuncs.c
***************
*** 13,21 ****
*/
#include "postgres.h"
! #include "access/htup.h"
#include "commands/trigger.h"
#include "utils/builtins.h"
/*
--- 13,25 ----
*/
#include "postgres.h"
! #include "executor/spi.h"
! #include "catalog/indexing.h"
! #include "commands/async.h"
#include "commands/trigger.h"
#include "utils/builtins.h"
+ #include "utils/fmgroids.h"
+ #include "utils/tqual.h"
/*
***************
*** 93,95 **** suppress_redundant_updates_trigger(PG_FUNCTION_ARGS)
--- 97,261 ----
return PointerGetDatum(rettuple);
}
+
+
+ /*
+ * Copy from s (for source) to r (for result), wrapping with q (quote)
+ * characters and doubling any quote characters found.
+ */
+ static char *
+ strcpy_quoted(char *r, const char *s, const char q)
+ {
+ *r++ = q;
+ while (*s)
+ {
+ if (*s == q)
+ *r++ = q;
+ *r++ = *s;
+ s++;
+ }
+ *r++ = q;
+ return r;
+ }
+
+ /*
+ * triggered_change_notification
+ *
+ * This trigger function will send a notification of data modification with
+ * primary key values. The channel will be "tcn" unless the trigger is
+ * created with a parameter, in which case that parameter will be used.
+ */
+ Datum
+ triggered_change_notification(PG_FUNCTION_ARGS)
+ {
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Trigger *trigger;
+ int nargs;
+ HeapTuple trigtuple,
+ newtuple;
+ HeapTupleHeader trigheader,
+ newheader;
+ Relation rel;
+ TupleDesc tupdesc;
+ Relation indexRelation;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple indexTuple;
+ char *channel;
+ char operation;
+ char payload[200];
+ char *p;
+ bool foundPK;
+
+ /* make sure it's called as a trigger */
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("triggered_change_notification: must be called as trigger")));
+
+ /* and that it's called after the change */
+ if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("triggered_change_notification: must be called after the change")));
+
+ /* and that it's called for each row */
+ if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("suppress_redundant_updates_trigger: must be called for each row")));
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ operation = 'I';
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ operation = 'U';
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ operation = 'D';
+ else
+ {
+ elog(ERROR, "suppress_redundant_updates_trigger: trigger fired by unrecognized operation");
+ operation = 'X'; /* silence compiler warning */
+ }
+
+ trigger = trigdata->tg_trigger;
+ nargs = trigger->tgnargs;
+ if (nargs > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("suppress_redundant_updates_trigger: must not be called with more than one parameter")));
+
+ if (nargs == 0)
+ channel = "tcn";
+ else
+ channel = trigger->tgargs[0];
+
+ /* get tuple data, set default result */
+ trigtuple = trigdata->tg_trigtuple;
+ newtuple = trigdata->tg_newtuple;
+
+ trigheader = trigtuple->t_data;
+ newheader = newtuple->t_data;
+
+ rel = trigdata->tg_relation;
+ tupdesc = rel->rd_att;
+
+ foundPK = false;
+
+ /* Prepare to scan pg_index for entries having indrelid = this rel. */
+ indexRelation = heap_open(IndexRelationId, AccessShareLock);
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ scan = systable_beginscan(indexRelation, IndexIndrelidIndexId, true,
+ SnapshotNow, 1, &skey);
+
+ while (HeapTupleIsValid(indexTuple = systable_getnext(scan)))
+ {
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ /* we're only interested if it is the primary key */
+ if (index->indisprimary)
+ {
+ int numatts = index->indnatts;
+
+ if (numatts > 0)
+ {
+ int i;
+
+ foundPK = true;
+
+ p = strcpy_quoted(payload, SPI_getrelname(rel), '"');
+ *p++ = ',';
+ *p++ = operation;
+
+ for (i = 0; i < numatts; i++)
+ {
+ int colno = index->indkey.values[i];
+
+ /* TODO: for UPDATE, check that the value wasn't updated? */
+
+ *p++ = ',';
+ p = strcpy_quoted(p, SPI_fname(tupdesc, colno), '"');
+ *p++ = '=';
+ p = strcpy_quoted(p, SPI_getvalue(trigtuple, tupdesc, colno), '\'');
+ }
+ *p = '\0';
+
+ Async_Notify(channel, payload);
+ }
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(indexRelation, AccessShareLock);
+
+ if (!foundPK)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("triggered_change_notification: must be called on a table with a primary key")));
+
+ return PointerGetDatum(NULL); /* after trigger; value doesn't matter */
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 1638,1643 **** DESCR("convert oid to int8");
--- 1638,1645 ----
DATA(insert OID = 1291 ( suppress_redundant_updates_trigger PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ suppress_redundant_updates_trigger _null_ _null_ _null_ ));
DESCR("trigger to suppress updates when new and old records match");
+ DATA(insert OID = 2650 ( triggered_change_notification PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ triggered_change_notification _null_ _null_ _null_ ));
+ DESCR("trigger function to send change notification with primary key in payload");
DATA(insert OID = 1292 ( tideq PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "27 27" _null_ _null_ _null_ _null_ tideq _null_ _null_ _null_ ));
DESCR("equal");
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 959,964 **** extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS);
--- 959,965 ----
/* trigfuncs.c */
extern Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS);
+ extern Datum triggered_change_notification(PG_FUNCTION_ARGS);
/* encoding support functions */
extern Datum getdatabaseencoding(PG_FUNCTION_ARGS);
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
