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