Re: How to watch for schema changes

On 12/2/18 5:24 AM, Igor Korot wrote:
> Hi, Adrian,
> Sorry for the delay to come back to this. I was busy doing other things.
>
> On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>> Hi, ALL,
>>> Is there any trigger or some other means I can do on the server
>>> which will watch for CREATE/ALTER/DROP TABLE command and after successful
>>> execution of those will issue a NOTIFY statement?
>>
>> https://www.postgresql.org/docs/10/static/event-triggers.html
>
> So if I understand correctly, I should write the trigger for the event
> I am interested in.
> And in this trigger I write a little SQL that will write the DDL
> command in some temporary table.
>
> I'm just looking for a way to execute this trigger and a function from
> my C++ code
> on the connection (either ODBC or thru the libpq).

Event triggers are fired by database events not by external prompting,
so you do not need to have your code execute the trigger. You do have
the option of disabling/enabling then though:

https://www.postgresql.org/docs/10/sql-altereventtrigger.html

>
> And then in my C++ code I will continuously query this temporary table.

Why a temporary table? They are session specific and if the session
aborts prematurely you will lose the information.

>
> Or there is a better alternative on the algorithm?
>
> Thank you.
>
>>>>
>>> Thank you.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com