On Mon, Dec 3, 2018 at 11:29 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > > On 12/3/18 8:16 AM, Igor Korot wrote: > > Hi, Adrian, > > > > On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > >> > >> 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.klaver@xxxxxxxxxxx> 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 > > . > > Yes, but the code to the event triogger needs to be written and then executed on > > connection, right? > > > > So, this is what I'm thingking: > > > > [code] > > ret = SQLExecute( m_hstmt, L"CREATE FUNCTION schema_watch_check() > > RETURNS event_trigger AS schema_watch LANGUAGE C", SQL_NTS ); > > if( ret == SQL_SUCCESS || ret== SQL_SUCCESS_WITH_INFO ) > > { > > ret = SQLExecute( m_hstmt, L"CREATE EVENT TRIGGER schema_watch ON > > ddl_command_end EXECUTE PROCEDURE schema_watch_check()", SQL_NTS ); > > } > > [/code] > > > > and something to that extent on the libpq connection. > > > > Am I missing something here? > > Yes this: > > https://www.postgresql.org/docs/9.6/sql-createeventtrigger.html > > "CREATE EVENT TRIGGER creates a new event trigger. Whenever the > designated event occurs and the WHEN condition associated with the > trigger, if any, is satisfied, the trigger function will be executed. > For a general introduction to event triggers, see Chapter 38. The user > who creates an event trigger becomes its owner." > > 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). > > > > > 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? 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.klaver@xxxxxxxxxxx > >>> > >> > >> > >> -- > >> Adrian Klaver > >> adrian.klaver@xxxxxxxxxxx > > > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx
