Ignoring temp table in Postgres event trigger

Question

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?

Answer 1

You'll have to exclude temporary objects by schema name:

FOR r IN
   SELECT * FROM pg_event_trigger_ddl_commands() AS ddl
   WHERE ddl.schema_name NOT LIKE 'pg_temp%'
LOOP
   ...
END LOOP;

The strange pattern is because temporary objects in PostgreSQL are created in schemas that are actually called pg_temp_1, pg_temp_2 and the like (a different one per user), and even though your example suggests that you always see pg_temp, I'd prefer to use the pattern just to be on the safe side.