2018-07-12 6:12 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com>: > On 07/11/2018 08:46 PM, Igor Korot wrote: > >> Hi, guys, >> >> >> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver <adrian.kla...@aklaver.com> >> wrote: >> >>> On 07/09/2018 01:49 PM, Igor Korot wrote: >>> >>>> >>>> Hi, Adrian >>>> >>>> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver < >>>> adrian.kla...@aklaver.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.kla...@aklaver.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 >>>> >>> >>> >>> Easy enough to test. As postgres super user: >>> >>> test_(postgres)# create trigger info_test before insert on pg_class >>> execute >>> procedure ts_update(); >>> >>> ERROR: permission denied: "pg_class" is a system catalog >>> >> >> But >> >> draft=# CREATE TRIGGER info_test AFTER INSERT ON >> information_schema.tables EXECUTE PROCEDURE test(); >> ERROR: function test() does not exist >> >> So it looks like this should be possible? >> > > No, see: > > https://www.postgresql.org/docs/10/static/sql-createtrigger.html > > AFTER trigger on views are STATEMENT level only. > > https://www.postgresql.org/docs/10/static/plpgsql-trigger.html > > "NEW > > Data type RECORD; variable holding the new database row for > INSERT/UPDATE operations in row-level triggers. This variable is unassigned > in statement-level triggers and for DELETE operations. > OLD > > Data type RECORD; variable holding the old database row for > UPDATE/DELETE operations in row-level triggers. This variable is unassigned > in statement-level triggers and for INSERT operations. > " > > So you won't know what was INSERTed in row. > > Moreover, there is nothing inserted into this view. It's inserted into pg_class, and an access to information_schema.tables just reads that catalog.
> >> Thank you. >> >> >>> >>> 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.kla...@aklaver.com >>>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Adrian Klaver >>>>> adrian.kla...@aklaver.com >>>>> >>>> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.kla...@aklaver.com >>> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Guillaume.
