Skip site navigation (1) Skip section navigation (2)

Avoiding execution of some functions by query rewriting

From: Thomas Girault <toma(dot)girault(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Avoiding execution of some functions by query rewriting
Date: 2012-05-16 12:30:00
Message-ID: CAMVHftSZDh+HZ9ag2F2H9K7RoS9dTySpDBVUa72Hi_wzbdj9FQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

I would like to allow the execution of a function (my_function) only if its
argument (my_table.x) belongs to a predefined interval (e.g. [100,1000]).

Let's take the following query example :
(q)  SELECT * FROM my_table WHERE my_function(mytable.x);

I would like this query automatically rewrites itself to check whether
"mytable.x" belong to the interval [100,1000] :
(q')  SELECT * FROM my_table WHERE (my_table.x BETWEEN 100 AND 1000) AND
my_function(my_table.x);

The command EXPLAIN ANALYSE shows that the second query is really faster
than the first one.

How can I change the query execution plan in order to automate the process
of query rewriting (q into q') ?
Where can I store suitably the metadata about the interval [100,1000]
associated to my_function ?

Thanks by advance,

Thomas Girault

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-05-16 12:35:51
Subject: Re: Avoiding execution of some functions by query rewriting
Previous:From: Heikki LinnakangasDate: 2012-05-16 11:46:17
Subject: Re: Interrupting long external library calls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group