On 02/22/2011 05:32 PM, Kevin Grittner wrote:
[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.
Have you performance tested it? Scanning pg_index for index columns for
each row strikes me as likely to be unpleasant.
Also, the error messages seem to need a bit of work (no wonder they
seemed familiar to me :) )
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
