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

-----Original Message-----
From: James Robinson [mailto:jlrobins(at)socialserve(dot)com]
Sent: Friday, March 25, 2016 11:29 AM
To: Igor Neyman <ineyman(at)perceptron(dot)com>
Cc: Melvin Davidson <melvin6925(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Way to get at parsed trigger 'WHEN' clause expression?

> On Mar 25, 2016, at 11:10 AM, Igor Neyman <ineyman(at)perceptron(dot)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. It sounds like folks don't know of something directly exposed at the SQL level as opposed to something available within the backend C only. When researching what psql's \d does, it was a pleasant surprise to see that the backend offers a single function 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’s tgqual 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 gets persisted. And then also pass in the oid of the table the trigger is on. But it seems it gets tripped up on how NEW and OLD 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 appear to be the WHEN clause portion' for this use case (an in-house web-based schema browser which just got taught how to display 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(at)jlr-photo(dot)com

_________________________________________________________________________________________________
James,

Instead of pg_catalog, you could use INFORMATION_SCHEMA to get "WHEN" clause:

select action_condition from information_schema.triggers where trigger_name = 'your_trigger_name';

Regards,
Igor