pg_trigger_depth()

A function returning the trigger nesting level

pg_trigger_depth() is a system function returning the current trigger nesting level.

pg_trigger_depth() was added in PostgreSQL 9.2.

Usage

pg_trigger_depth () → integer

Change history

Examples

Contrived example to demonstrate pg_trigger_depth():

postgres=# CREATE TABLE foo (id INT, val TEXT);
CREATE TABLE

postgres=# CREATE OR REPLACE FUNCTION trigger_depth()
             RETURNS TRIGGER
             LANGUAGE plpgsql
           AS $$
             DECLARE
               depth INT;
             BEGIN
               depth := pg_trigger_depth();
               RAISE NOTICE 'depth: %', depth;
               IF depth = 1 THEN
                  INSERT INTO foo VALUES(-1, 'test');
               END IF;
               RETURN NEW;
             END;
           $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER foo_ins_trigger
             BEFORE INSERT
             ON foo
             FOR EACH ROW
             EXECUTE FUNCTION trigger_depth(); -- PostgreSQL 10 and earlier: EXECUTE PROCEDURE trigger_depth();
CREATE TRIGGER

postgres=# INSERT INTO foo values(1,'foo');
NOTICE:  depth: 1
NOTICE:  depth: 2
INSERT 0 1

If executed outside of a trigger, pg_trigger_depth() returns 0:

postgres=# SELECT pg_trigger_depth();
 pg_trigger_depth
------------------
                0
(1 row)

Categories

System function, Trigger

See also

trigger