Thomas Girault <toma.girault@gmail.com> writes:
> 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 ?
Surely a quick "if" test added to the top of the function would be
a better solution than trying to hack the system to do this. Keep
in mind that any code you add to the planner for such a thing would
fire on every function call, in every query, all the time. It seems
unlikely that the possible benefit of not entering your function at
all would outweigh the distributed penalty from the added planner
overhead.
regards, tom lane