Hi, Adrian,
On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver <adrian.klaver@aklaver.com> 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@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
> >
> > 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?
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?
>
> >
> > 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@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
