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

Folks,

    I see that psql's \d displays trigger information of a table by making a call to pg_catalog.pg_get_triggerdef(),
whichabstracts away most all need to parse the contents of system catalog pg_trigger. However, we'd like to be able to
getat a human readable representation of just the WHEN clause of the trigger expression. Function pg_get_expr() looked
likely,but fails when fed a pgqual value from pg_trigger ala: 

    ERROR: bogus varno: 2

    I suspect that it is falling over dead trying to parse the representation of NEW and / or OLD. Anything built in,
orshould we just make call to pg_catalog.pg_get_triggerdef() and parse out the WHEN clause text? 

Thanks!
------
James Robinson
Socialserve.com by Emphasys Software

> 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

> On Mar 25, 2016, at 3:02 PM, Igor Neyman <ineyman@perceptron.com> wrote:
>
> 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

Hah, good suggestion. Looking into how *it* is implemented (on 9.2 at least), brings us back to string manipulation of
theresult of pg_get_triggerdef(): 

\d+ information_schema.triggers
...
"substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text)
+47)::information_schema.character_data AS action_statement, 
...

But at least we can turn a blind eye to its internal horror. Thanks!

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

-----Original Message-----
From: James Robinson [mailto:jlrobins@socialserve.com] 
Sent: Friday, March 25, 2016 11:29 AM
To: Igor Neyman <ineyman@perceptron.com>
Cc: Melvin Davidson <melvin6925@gmail.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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

_________________________________________________________________________________________________
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