[moving to -hackers with BC to -general]
Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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