Subject: How to get a notification

From: Igor Korot <[email-masked]>
Date: Thu, 6 Feb 2025 01:21:17 -0600

Hi, ALL,
In my code I'm running following:

            queries.push_back( L"CREATE FUNCTION
__watch_schema_changes() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN NOTIFY tg_tag; END; $$;" );
            queries.push_back( L"CREATE EVENT TRIGGER
schema_change_notify ON ddl_command_end WHEN TAG IN(\'CREATE TABLE\',
\'ALTER TABLE\', \'DROP TABLE\', \'CREATE INDEX\', \'DROP INDEX\')
EXECUTE PROCEDURE __watch_schema_changes();" );

My questions are:
1 Is there a better way to get notification about CREATE/ALTER/DROP TABLE?
2. How do I receive notification abut the event with the object name?

Thank you.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 7 Feb 2025 09:07:35 -0800

> 1 Is there a better way to get notification about CREATE/ALTER/DROP TABLE?

An alternate solution:

https://www.pgaudit.org/

Whether it is better or not is something you will need to decide.

> 2. How do I receive notification abut the event with the object name?

Use information from here?:

https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS

-- 
Adrian Klaver
[email-masked]

---

From: Igor Korot <[email-masked]>
Date: Fri, 7 Feb 2025 13:02:19 -0600

Hi, Adrian,

> https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS

Thx for the link
So basically all I need to do is to call

SELECT pg_event_trigger_ddl_commands ()

right?

In both ODBC amd libpq interfaces?

Thank you.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 7 Feb 2025 11:13:16 -0800

> SELECT pg_event_trigger_ddl_commands ()

And then unpack the information it returns.

> In both ODBC amd libpq interfaces?

It is not about the interface it is about, from link above:

"pg_event_trigger_ddl_commands returns a list of DDL commands executed 
by each user action, when invoked in a function attached to a 
ddl_command_end event trigger."

---

From: Igor Korot <[email-masked]>
Date: Fri, 7 Feb 2025 13:43:24 -0600

Hi, Adrian,

> ddl_command_end event trigger."

Is there a sample code i can look at?
Also what is PostgreSQL version this function is introduced?

Thank you.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 7 Feb 2025 13:16:08 -0800

> Is there a sample code i can look at?

Search:

https://duckduckgo.com/?q=pg_event_trigger_ddl_commands+example&t=ffab&ia=web

and ye shall find:

https://stackoverflow.com/questions/23488228/how-to-get-sql-text-from-postgres-event-trigger

> Also what is PostgreSQL version this function is introduced?

At top  of Postgres doc pages are version specific links.

Go to:

https://www.postgresql.org/docs/current/functions-event-
triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS

and click back on versions until you don't find it.