public inbox for [email protected]
help / color / mirror / Atom feedHow to get a notification
6+ messages / 2 participants
[nested] [flat]
* How to get a notification
@ 2025-02-06 07:21 Igor Korot <[email protected]>
2025-02-07 17:07 ` Re: How to get a notification Adrian Klaver <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Igor Korot @ 2025-02-06 07:21 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a notification
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
@ 2025-02-07 17:07 ` Adrian Klaver <[email protected]>
2025-02-07 19:02 ` Re: How to get a notification Igor Korot <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2025-02-07 17:07 UTC (permalink / raw)
To: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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-E...
>
> Thank you.
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a notification
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
2025-02-07 17:07 ` Re: How to get a notification Adrian Klaver <[email protected]>
@ 2025-02-07 19:02 ` Igor Korot <[email protected]>
2025-02-07 19:13 ` Re: How to get a notification Adrian Klaver <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Igor Korot @ 2025-02-07 19:02 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, Adrian,
On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <[email protected]> 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-E...
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.
>
> >
> > Thank you.
> >
> >
>
> --
> Adrian Klaver
> [email protected]
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a notification
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
2025-02-07 17:07 ` Re: How to get a notification Adrian Klaver <[email protected]>
2025-02-07 19:02 ` Re: How to get a notification Igor Korot <[email protected]>
@ 2025-02-07 19:13 ` Adrian Klaver <[email protected]>
2025-02-07 19:43 ` Re: How to get a notification Igor Korot <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2025-02-07 19:13 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On 2/7/25 11:02 AM, Igor Korot wrote:
> Hi, Adrian,
>
> On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <[email protected]> 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-E...
>
> 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
>> [email protected]
>>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a notification
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
2025-02-07 17:07 ` Re: How to get a notification Adrian Klaver <[email protected]>
2025-02-07 19:02 ` Re: How to get a notification Igor Korot <[email protected]>
2025-02-07 19:13 ` Re: How to get a notification Adrian Klaver <[email protected]>
@ 2025-02-07 19:43 ` Igor Korot <[email protected]>
2025-02-07 21:16 ` Re: How to get a notification Adrian Klaver <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Igor Korot @ 2025-02-07 19:43 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, Adrian,
On Fri, Feb 7, 2025, 1:13 PM Adrian Klaver <[email protected]>
wrote:
>
>
> On 2/7/25 11:02 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Fri, Feb 7, 2025 at 11:07 AM Adrian Klaver <[email protected]>
> 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-E...
> >
> > 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.
>
>
> >
> > Thank you.
> >
> >
> >
> >>
> >>>
> >>> Thank you.
> >>>
> >>>
> >>
> >> --
> >> Adrian Klaver
> >> [email protected]
> >>
>
> --
> Adrian Klaver
> [email protected]
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a notification
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
2025-02-07 17:07 ` Re: How to get a notification Adrian Klaver <[email protected]>
2025-02-07 19:02 ` Re: How to get a notification Igor Korot <[email protected]>
2025-02-07 19:13 ` Re: How to get a notification Adrian Klaver <[email protected]>
2025-02-07 19:43 ` Re: How to get a notification Igor Korot <[email protected]>
@ 2025-02-07 21:16 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-02-07 21:16 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
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&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.
>
> Thank you.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-02-07 21:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-06 07:21 How to get a notification Igor Korot <[email protected]>
2025-02-07 17:07 ` Adrian Klaver <[email protected]>
2025-02-07 19:02 ` Igor Korot <[email protected]>
2025-02-07 19:13 ` Adrian Klaver <[email protected]>
2025-02-07 19:43 ` Igor Korot <[email protected]>
2025-02-07 21:16 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox