postgres event trigger and function not working

Question

i looked for a event trigger which fired up after creation of a table. I found some good posts here and do some test. My problem is that nothing happens, no errors nor output. So please give some advice what i do wrong. Here my code. I'am complete new in postgres, come from oracle, so please forgive me for this maybe "simple" question.

create table createt (tname char(20));

CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE r RECORD;
BEGIN
r := pg_event_trigger_ddl_commands();
INSERT INTO createt VALUES(r.object_identity);
END;
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE insert();

So in this testcase i want to put the tablename of the newly createt table in the table createt! But nothing happens. How can i check if the trigger is fired up. Or how can i debug the function?

Thanks for your time and advices.

klin · Accepted Answer · 2018-04-26 15:28:44Z

4

The function pg_event_trigger_ddl_commands() returns set of rows. The trigger function must be ready to accept more than a single row, e.g.:

CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
BEGIN
    INSERT INTO createt 
    SELECT object_identity
    FROM pg_event_trigger_ddl_commands();
END;
$$
LANGUAGE plpgsql;

char(20) is not enough for object_identity, use text:

DROP TABLE createt;
CREATE TABLE createt (tname text);

What happens when you create a table with serial primary key?

create table my_table(id serial primary key);

Let's check:

select *
from createt

         tname          
------------------------
 public.my_table_id_seq
 public.my_table
 public.my_table_pkey
 public.my_table_id_seq
(4 rows)

There are two actions on the sequence, first to crete it and the other to set owned by with alter sequence.

edited Apr 26, 2018 at 15:28

answered Apr 26, 2018 at 14:12

klin

123k15 gold badges241 silver badges263 bronze badges

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

Comments

MarcelS · Accepted Answer · 2018-04-26 15:16:16Z

0

thanks for your advice. Very good explanation about the rowset for object_identity. But why there are 4 rows in the table createt. I would expect 3 (tablename, Primary key name, index_name). Why there is the "my_table_id_seq" two times insert?

So i used your code an try it for me but now i get erros that the function pg_event_trigger_ddl_commands does not exist!

marcel=# DROP TABLE createt;
FEHLER:  Tabelle »createt« existiert nicht
marcel=# CREATE TABLE createt (tname text);
CREATE TABLE
marcel=#
marcel=# CREATE OR REPLACE FUNCTION insert()
marcel-# RETURNS event_trigger
marcel-# AS $$
marcel$# BEGIN
marcel$# INSERT INTO createt
marcel$# SELECT object_identity
marcel$# FROM pg_event_trigger_ddl_commands();
marcel$# END;
marcel$# $$
marcel-# LANGUAGE plpgsql;
CREATE FUNCTION
marcel=# CREATE EVENT TRIGGER insert_event ON ddl_command_end
marcel-# WHEN TAG IN ('CREATE TABLE')
marcel-# EXECUTE PROCEDURE insert();
CREATE EVENT TRIGGER
marcel=#
marcel=#
marcel=# create table my_table(id serial primary key);
FEHLER:  Funktion pg_event_trigger_ddl_commands() existiert nicht
ZEILE 3: FROM pg_event_trigger_ddl_commands()
              ^
TIP:  Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
ANFRAGE:  INSERT INTO createt
SELECT object_identity
FROM pg_event_trigger_ddl_commands()
KONTEXT:  PL/pgSQL-Funktion insert() Zeile 3 bei SQL-Anweisung
marcel=#
marcel=# select * from createt;
 tname
-------
(0 Zeilen)

edited Apr 26, 2018 at 15:16

answered Apr 26, 2018 at 14:56

MarcelS

1121 silver badge9 bronze badges

4 Comments

klin Over a year ago

The function pg_event_trigger_ddl_commands() was introduced in Postgres 9.5. What's the version of your server?

MarcelS Over a year ago

I have 9.4.15... this explains everything ;)

MarcelS Over a year ago

Could you please answer why there are 4 rows for this table? And is there a another way for the missing tunction in my version? Ah... i see your update. thanks for this.

klin Over a year ago

See the updated answer, i've added a note at the end.

Collectives™ on Stack Overflow

postgres event trigger and function not working

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related