Re: Extension to rewrite queries before execution - Mailing list pgsql-general

> On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

 

On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one.

The context is running a third-party app which issues queries I have no control over.  I'd like to intercept a specific query (which has no bind parameters) and either replace the query text with a different text which, for example, swaps out an "in list" clause to instead be an "exists (subquery)". 

Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;" before and a reset of it after.

Is there anything out there like this?  This would be for 9.4.

I'm willing to put the query text, and its replacement, directly into the extension source code and compile it, but of course something more flexible would be ideal.

You have not stated which Version or PostgreSQL, nor the O/S involved. That being said, depending on what the specific query is, you might consider using a Rule or Trigger to handle it. If you use a Trigger ( which is the preferred method) you can also embed "set" commands the associated function.

Sure I did, 9.4.  OS would be Linux, although I would hope a solution would be work.   If you mean the built-in-to-core rules or triggers, those wouldn't work.  Neither one allows you to rewrite a where clause as far as I can tell.  Rules allows you add one, but not more than that.  And triggers don't exists for select queries.

Cheers,

Jeff