Re: How to watch for schema changes

Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>
>> Adrian,
>>
>> 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
>>
>>
>> According to the documentation the lowest version it supports is 9.3.
>> Anything prior to that?
>>
>> I'm working with OX 10.8 and it has 9.1 installed.
>
> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
> though it will go EOL this September:
>
> https://www.postgresql.org/support/versioning/
>
> Are you forced to work with 9.1 or can you use something from here:
>
> https://www.postgresql.org/download/macosx/
>
> to get a newer version? FYI that will be a major upgrade so will require a
> dump/restore or use of pg_upgrade.

Just a thought...
Is it possible to create a trigger for a system table? Or this
operation is restricted
for when the server is actually being set-up?

Successful "CREATE TABLE..." statement creates a row inside the
information_schema.tables
so if I can create a trigger after this record is inserted or deleted
that should be fine.

Thank you.

>
>>
>> And a second question - how do I work with it?
>> I presume that function will have to be compiled in its own module
>> (either dll, so or dylib).
>> But then from the libpq interface how do I call it?
>
>
> It can use functions written in PL languages. See below:
>
> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> for an example written in plpgsql.
>
>
>
>
>
>>
>> Thank you.
>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com