Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il> writes:
> CREATE OR REPLACE FUNCTION test_func(STR text)
> ...
> perform t1.val FROM t1 WHERE
> (COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
> order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
[ doesn't use index ]
No, it doesn't. The LIKE index optimization requires the LIKE pattern
to be a constant at plan time, so that the planner can extract the
pattern's fixed prefix. An expression depending on a function parameter
is certainly not constant.
If you really need this to work, you could use EXECUTE USING so that
the query is re-planned for each execution.
regards, tom lane