Re: Avoiding execution of some functions by query rewriting

On May16, 2012, at 14:30 , Thomas Girault wrote:
> 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);

Rename my_function to my_function_impl or the like, and create a SQL-language
wrapper function which does

SELECT (my_table.x BETWEEN 100 AND 1000) AND my_function_impl(my_table.x)

i.e. do

CREATE FUNCTION my_function(sometype) RETURNS BOOL AS $$
SELECT ($1 BETWEEN 100 AND 1000) AND my_function_impl($1)
$$ LANGUAGE SQL IMMUTABLE;

This works because sufficiently simply SQL-language functions are inlined early during
query planning, which allows later stages to see through the function call and e.g.
use an index range scan to pull only those rows which satisfy a BETWEEN clause.

I don't remember the precise conditions under which SQL-language functions are inlined,
but I'm pretty sure that not referencing any tables and not having a FROM clause is
sufficient. Note that inlining *only* happens for SQL-language functions, *not* for
PLPGSQL functions, even if the latter consist of a simple RETURN statement.

best regards,
Florian Pflug