I would like to know which table stores the create statement of a trigger for event trigger eg

Question

For example, I create such a trigger:

CREATE EVENT TRIGGER tr_event_begin 
ON ddl_command_start 
EXECUTE PROCEDURE event_trigger_begin ( );

From which table can now be pulled in the same form? Or in parts at least. information_schema.triggers does not contain it since it is an event trigger, rather than an ordinary trigger.

See [How to check if trigger exists in PostgreSQL?](https://stackoverflow.com/a/33174794/1995738) — klin, Oct 14 '17 at 14:33
This will work with normal triggers but I need to event triggers. — Виктор, Oct 14 '17 at 14:37
Possible duplicate of [How to check if trigger exists in PostgreSQL?](https://stackoverflow.com/questions/33174638/how-to-check-if-trigger-exists-in-postgresql) — pegla, Oct 14 '17 at 14:38

Vao Tsun · Accepted Answer · 2017-10-14T22:25:26.643

sample:

et=# create or replace function event_trigger_begin() returns event_trigger as $$ begin raise info '%',99; end;$$ language plpgsql;
CREATE FUNCTION
Time: 27.611 ms
et=# CREATE EVENT TRIGGER tr_event_begin
ON ddl_command_start
EXECUTE PROCEDURE event_trigger_begin ( );
CREATE EVENT TRIGGER
Time: 18.293 ms
et=# create table i (i int);
INFO:  99
CREATE TABLE

You can use pg_catalog relation to generate DDL for event_triggers, smth like:

et=# SELECT format('CREATE EVENT TRIGGER %I ON %s EXECUTE PROCEDURE %I()',evtname,evtevent,evtfoid::regproc) as ddl
FROM pg_event_trigger;
                                               ddl
--------------------------------------------------------------------------------------------------
 CREATE EVENT TRIGGER tr_event_begin ON ddl_command_start EXECUTE PROCEDURE event_trigger_begin()
(1 row)

and lastly the right way to get definition:

MacBook-Air:~ vao$ pg_dump -d et -s | tail -20

CREATE FUNCTION event_trigger_begin() RETURNS event_trigger
    LANGUAGE plpgsql
    AS $$ begin raise info '%',99; end;$$;


ALTER FUNCTION public.event_trigger_begin() OWNER TO vao;

--
-- Name: tr_event_begin; Type: EVENT TRIGGER; Schema: -; Owner: vao
--

CREATE EVENT TRIGGER tr_event_begin ON ddl_command_start
   EXECUTE PROCEDURE public.event_trigger_begin();

of course dbname will be not et and tail will not work - I used it jsut to truncate output

@Виктор no worries, please approve and/or upvote if answered — Vao Tsun, Oct 15 '17 at 08:53

I would like to know which table stores the create statement of a trigger for event trigger eg

1 Answers1