Re: Re: [GENERAL] How to extract a value from a record using attnum or attname? - Mailing list pgsql-hackers

On 02/22/2011 05:32 PM, Kevin Grittner wrote:
> [moving to -hackers with BC to -general]
>
> Dimitri Fontaine<dimitri@2ndQuadrant.fr>  wrote:
>> "Kevin Grittner"<Kevin.Grittner@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