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

On Thu, Mar 24, 2016 at 6:58 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Thu, Mar 24, 2016 at 4:05 PM, James Robinson <jlrobins(at)socialserve(dot)com>
> wrote:
>
>> Folks,
>>
>> I see that psql's \d displays trigger information of a table by
>> making a call to pg_catalog.pg_get_triggerdef(), which abstracts away most
>> all need to parse the contents of system catalog pg_trigger. However, we'd
>> like to be able to get at 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, or should 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
>>
>>
>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> Your problem description is a bit cloudy. Could you please give just a
> little more specific and provide
> 1. Your PostgreSQL version?
> 2. Your O./S ?
> 3. The exact trigger definition?
> 4. Your exact SQL query for pg_get_triggerdef() ?
>
> Little things like that make it so much easier to provide solutions.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>

Did you mean something like

*SELECT substring(pg_get_triggerdef(tg.oid, TRUE) FROM position('WHEN' in
pg_get_triggerdef(tg.oid, TRUE))) AS when_cond FROM pg_trigger tg WHERE
tg.tgname = 'your_trigger_name';*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.