How to get new table name inside trigger in PostgreSQL

Question

I tried two ways to get table name inside Trigger function. But both of them not working. My Postgresql version is 9.5.14

1)

CREATE OR REPLACE FUNCTION log_ddl2()
  RETURNS event_trigger AS $$
DECLARE
  audit_query TEXT;
  r RECORD;
BEGIN
  IF tg_tag = 'CREATE TABLE'
  THEN
    r := pg_event_trigger_ddl_commands();
    INSERT INTO ddl_history(object_name) VALUES(r.object_identity);
  END IF;
END;

$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_ddl_info2 ON ddl_command_end EXECUTE PROCEDURE log_ddl2();



CREATE TABLE mytable(
    id serial PRIMARY KEY, 
    first_name text,
    age integer,
    created_at timestamp(0) without time zone default now()
 );

Above way is giving an error:

pg_event_trigger_ddl_commands() returning set of rows

Second way is using TG_TABLE_NAME::regclass::text. This also not working. It is giving TG_TABLE_NAME does not exists. Is there another possible way to get table name once it is created?

Laurenz Albe · Accepted Answer · 2021-01-19 07:00:16Z

2

Try

SELECT * INTO r
FROM pg_event_trigger_ddl_commands()
WHERE object_type = 'table';

I am not sure if object_type is in lower case.

edited Jan 19, 2021 at 7:00

answered Jan 19, 2021 at 6:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

trempo Over a year ago

It returned public.mytable_id_seq.

Laurenz Albe Over a year ago

I have amended the answer.

trempo Over a year ago

Yes now it is working fine. Thank you very much!

Collectives™ on Stack Overflow

How to get new table name inside trigger in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related