Re: Way to get at parsed trigger 'WHEN' clause expression?

> On Mar 25, 2016, at 11:10 AM, Igor Neyman <ineyman@perceptron.com> wrote:
>
> This doesn’t answer OP question.
>
> Besides this query gets you not only “WHEN” clause but also whatever follows it, for instance “EXECUTE PROCEDURE…”

Yeah. I was imagining having to do doing something semantically equivalent, but better using some more subtle regexes.
Itsounds like folks don't know of something directly exposed at the SQL level as opposed to something available within
thebackend C only. When researching what psql's \d does, it was a pleasant surprise to see that the backend offers a
singlefunction to produce the entire trigger representation. Now alas we ended up wanting just a portion of it. 

>
> As for “pg_get_expr(pg_node_tree, relation_oid)” – looks like it doesn’t work with pg_trigger, because as a second
parameter(Var) it expects relation_oid, and relation could have multiple triggers, so pg_get_expr() wouldn’t know which
trigger’stgqual you want to decompile. 
>

pg_get_expr() can be fed the pg_catalog.pg_trigger.tgqual value, which looks to be the column where the WHERE clause
getspersisted. And then also pass in the oid of the table the trigger is on. But it seems it gets tripped up on how NEW
andOLD are represented, 'cause those are not just column references. 

I'm content with going down 'use pg_get_triggerdef(), then work to strip out all of the string contents which does not
appearto be the WHEN clause portion' for this use case (an in-house web-based schema browser which just got taught how
todisplay triggers). Was primarily interested in seeing if there was a known way of doing this short of filthy string
parsing.

I'll post the soln. I end up with just for mail archives search fodder completeness.

Thanks folks!




-------
James Robinson
james@jlr-photo.com