Alvaro Herrera wrote:
> Tom Lane wrote:
>
>> Oh! What is happening is that to_number(1) is being reduced to constant
>> NULL, whereupon it concludes that ad_tab_id=to_number(1) is constant
>> NULL, ergo the EXISTS can never succeed, ergo the entire WHERE is
>> constant false.
>
>> CREATE FUNCTION to_number(integer) RETURNS numeric
>> LANGUAGE plpgsql IMMUTABLE
>> AS $_$
>> BEGIN
>> RETURN to_number($1, 'S99999999999999D999999');
>> EXCEPTION
>> WHEN OTHERS THEN
>> RETURN NULL;
>> END;
>> $_$;
>
> How can the executor "see through" a plpgsql function definition? Or do
> you mean that this reduction is being done at execution time? (hmm ...
> this is what a one-time filter is, now that I think about it?)
Note that the function is immutable, and the argument in the query is a
constant. The planner simply runs the function and replaces the function
call with the result.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com