Thread: Using pg_trigger.tgqual

Hello,

I was interested in reading pg_trigger.tgqual from the system catalogs to
get a trigger WHEN expression. E.g., for

CREATE TRIGGER tr_example
       BEFORE UPDATE
       ON example
       FOR EACH ROW
       WHEN (NEW.num > 5)
       EXECUTE PROCEDURE tr_example_fn();

I would love to receive the "NEW.num > 5" expression in my SELECT FROM
pg_trigger query.

I tried to:
SELECT pg_get_expr(tgqual, tgrelid) FROM pg_trigger
which reports "ERROR: bogus varno: 2", though. I am not aware of anything
better to pass as the second argument to pg_get_expr() - with any other
reasonable column I tried the result is NULL.

I know there is the pg_get_triggerdef(trigger_oid) function, which returns
the whole trigger definition. I am only interested in the WHEN expression,
though. Of course I can parse the WHEN expression from that, but I hoped
the pg_trigger.tgqual field could be usable. It seems to me there is a
problem with respect to the NEW/OLD variables. (When neither NEW nor OLD
is referenced, pg_get_expr() returns the expression alright - which is not
surprising, though...)

information_schema.triggers.action_condition is not an option for me since
it does not contain TRUNCATE triggers.

Could you, please, make any suggestions on what to pass to pg_get_expr()
to return the expression using NEW/OLD references?

Thank you,
Ondřej Bouda