Using pg_trigger.tgqual

From: Ondřej Bouda <obouda(at)email(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Using pg_trigger.tgqual
Date: 2014-02-16 18:17:57
Message-ID: op.xbdzr7xi6unlds@gracie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Haribabu Kommi 2014-02-17 03:16:33 Re: Toast and slice of toast
Previous Message Ondřej Bouda 2014-02-16 18:17:09 Using pg_trigger.tgqual