sqlpostgresqlpostgresql-triggers

How to execute same query in remote postgres server from inside "BEFORE TRIGGER"


I am planning to run exact same queries on the remote database server exactly when query executing on local server from inside BEFORE INSERT,UPDATE and DELETE TRIGGERs.

My use case is something like:

  1. Using db_link for making connection to remote server
  2. want to execute same query to remote server before proceeding further in local server.

You might tell me to use OLD and NEW object to format data in new query but I have a situation here in UPDATE and DELETE query, in that we are using WHERE clause and this is not consistent because using which column we are going to perform the operation is not known e.g.

DELETE FROM my_table WHERE col1 = 'possibility1' OR col2 = 'possibility2'

and another possibility

DELETE FROM my_table WHERE col1 = 'possibility1' AND col3 = 'possibility2'

Now question is how to access this complete query inside trigger to run same on remote server


Solution

  • Figured out the solution for this question. I am using current_query() inside trigger to get the exact query string that caused trigger to invoke.

    e.g.

    RAISE NOTICE 'my exact query statement: %', current_query();