[moving to -hackers with BC to -general]
Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
Kevin Grittner kevin.gritt...@wicourts.gov 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;
+ Relationrel;
+ TupleDesc tupdesc;
+ RelationindexRelation;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple indexTuple;
+ char *channel;
+ charoperation;
+ charpayload[200];
+ char *p;
+ boolfoundPK;
+
+ /* 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),
+
