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

In response to

• Avoiding execution of some functions by query rewriting at 2012-05-16 12:30:00 from Thomas Girault

pgsql-hackers by date

Next:	From: Tom Lane	Date: 2012-05-16 13:08:26
	Subject: Re: 9.2beta1 regression: pg_restore --data-only does not set sequence values any more
Previous:	From: Sandro Santilli	Date: 2012-05-16 12:42:45
	Subject: Re: Interrupting long external library calls