PG DDL event trigger does not work properly

Question

I am trying to intercept a CREATE TABLE by an event trigger in PostgreSQL, to forbid the creation of table that does not comply to some naming rules. My code is as follow:

CREATE OR REPLACE FUNCTION e_ddl_create_table_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;    
BEGIN
   FOR obj IN SELECT * 
              FROM   pg_event_trigger_ddl_commands() 
              WHERE  command_tag in ('CREATE TABLE')
   LOOP
        if  NOT  obj.object_identity LIKE 't?_%' ESCAPE '?'
        THEN
           raise EXCEPTION 'The table name must begin with t_';
        end if;
   END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE e_ddl_create_table_func();

When I try with:

CREATE TABLE t_toto3 (i INT)

I have systematically the following error:

ERROR: The table name must begin with t_
CONTEXT: fonction PL/pgSQL e_ddl_create_table_func(), ligne 11 à RAISE

What am I missing ?

AdamKG · Accepted Answer · 2020-10-09 15:31:37Z

3

Per the docs, object_identity is schema-qualified. It will be coming in as 'public.t_toto3' in your example (unless you have a very nonstandard setup with some other default schema); you can get only the table component by passing it through parse_ident() and extracting the 2nd item. (Note the extra parens around the final parse_ident() so that the array lookup is parsed correctly.)

testdb=# select 'public.t_toto3' LIKE 't?_%' ESCAPE '?';
 ?column? 
----------
 f
(1 row)

testdb=# select parse_ident('public.t_toto3');
   parse_ident    
------------------
 {public,t_toto3}
(1 row)

testdb=# select (parse_ident('public.t_toto3'))[2] LIKE 't?_%' ESCAPE '?';
 ?column? 
----------
 t
(1 row)

edited Oct 9, 2020 at 15:31

answered Oct 9, 2020 at 15:02

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Alternatively, parse_ident could be used to split the name into schema a table name

SQLpro · Accepted Answer · 2020-10-09 16:10:38Z

yes it works with parse_ident. The solution is :

CREATE OR REPLACE FUNCTION e_ddl_create_table_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;    
BEGIN
   FOR obj IN SELECT * 
              FROM   pg_event_trigger_ddl_commands() 
              WHERE  command_tag in ('CREATE TABLE')
   LOOP
        if  NOT  (parse_ident(obj.object_identity))[2] LIKE 't?_%' ESCAPE '?'
        THEN
           raise EXCEPTION 'The table name must begin with t_';
        end if;
   END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE e_ddl_create_table_func();

Collectives™ on Stack Overflow

PG DDL event trigger does not work properly

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related