Re: BUG #16015: information_schema.triggers lack of truncate trigger

Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> writes:
> This is what I found in the definition of triggers views in
> information_schema.sql which says we are intentionally omitting
> truncate triggers but then there is no clear cut explanation given for
> this omission.

The reason is that TRUNCATE triggers aren't in the SQL standard, so
they can't be shown by a spec-compliant view. Concretely, SQL:2011
part 11 section 6.62 ("TRIGGERS base table") shows that column as
having this definition:

EVENT_MANIPULATION INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT TRIGGERS_EVENT_MANIPULATION_CHECK
CHECK ( EVENT_MANIPULATION IN
( 'INSERT', 'DELETE', 'UPDATE' ) ),

Some other DBMSes think it's okay to bend the definition of the
information_schema views so that they can show non-spec-compliant
objects, but we don't. The point of those views, according to our
policy, is to be useful to portable (spec-compliant) applications
which presumably will only be creating spec-compliant objects.
If somebody makes non-spec-compliant objects in the same database,
and we show them in the information_schema, such an application
could get mightily confused by what it sees there.

In short, if you want to make Postgres-specific objects like
ON TRUNCATE triggers, use Postgres-specific system catalog
queries to introspect them.

regards, tom lane