Sample Header Ad - 728x90

Ignoring temp table in Postgres event trigger

2 votes
1 answer
601 views
I am trying to have a trigger that gets invoked when new tables, except temporary tables, are created. This is what I have tried:
CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'ALTER TABLE', 'DROP TABLE')
 EXECUTE PROCEDURE insert();

create TEMP table my_table(id serial primary key);
This is the output I see:
CREATE TABLE
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE FUNCTION
CREATE EVENT TRIGGER
NOTICE:  caught CREATE SEQUENCE event on pg_temp.my_table_id_seq
NOTICE:  caught CREATE TABLE event on pg_temp.my_table
NOTICE:  caught CREATE INDEX event on pg_temp.my_table_pkey
NOTICE:  caught ALTER SEQUENCE event on pg_temp.my_table_id_seq
How do I exclude temporary tables from invoking the trigger?
Asked by user (131 rep)
Apr 19, 2021, 02:47 PM
Last activity: Apr 19, 2021, 03:15 PM