Re: How to watch for schema changes

On 12/3/18 9:53 AM, Igor Korot wrote:
>> So event triggers are associated with
>> events(www.postgresql.org/docs/9.6/event-trigger-matrix.html) in a
>> particular database. A rough description is that they are triggers on
>> changes to the system catalogs.
>> You could, I guess, create and drop them for each connection. To me it
>> would seem more efficient to create them once. You then have the choice
>> of leaving them running or using the ALTER command I posted previously
>> to ENABLE/DISABLE them.
>
> OK, so how do I do it?
> There is no "CREATE EVENT TRIGGER IF NOT EXIST".
>
> As I say - I'm trying to make it work from both ODBC and libpq
> connection (one at a time)

Why? Just create the trigger once in a script. Event triggers are an
attribute of the database and stay with it until they are dropped. If
you want to turn then on and off use the ALTER EVENT TRIGGER
ENABLE/DISABLE. If you insist on recreating them on each connection then:

https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
CREATE EVENT TRIGGER ...

>
>>
>>>
>>> Now the other question is - how to make it work?
>>> I can write the function code, compile it and place in the folder
>>> where my executable is (or it should be in some postgreSQL dir?) and
>>> then executing above code
>>> will be enough. Is this correct?
>
> Also - what about this?
>
> And why did you say that saving the SQL commend is not a good idea.
>
> What's better?

See above.

>
> Thank you.
>
>>>
>>>>
>>>>>
>>>>> 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.
>>>
>>> Is there a better alternative?
>>>
>>> Thank you.
>>>
>>>>
>>>>>
>>>>> 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
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com

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