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.
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
.