How to get a notification

On 2/5/25 23:21, Igor Korot wrote:

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?

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

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Adrian,

On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 2/5/25 23:21, Igor Korot wrote:

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?

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

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.

On 2/7/25 11:02 AM, Igor Korot wrote:

Hi, Adrian,

On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 2/5/25 23:21, Igor Korot wrote:

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?

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

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

SELECT pg_event_trigger_ddl_commands ()

And then unpack the information it returns.

right?

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

Thank you.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Adrian,

On Fri, Feb 7, 2025, 1:13 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/7/25 11:02 AM, Igor Korot wrote:

Hi, Adrian,

On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <adrian.klaver@aklaver.com>

wrote:

On 2/5/25 23:21, Igor Korot wrote:

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?

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

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

SELECT pg_event_trigger_ddl_commands ()

And then unpack the information it returns.

right?

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

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

Thank you.

On 2/7/25 11:43, Igor Korot wrote:

Hi, Adrian,

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

Is there a sample code i can look at?

Search:

https://duckduckgo.com/?q=pg_event_trigger_ddl_commands+example&amp;t=ffab&amp;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.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com