Re: Full text search, SQL functions, and the planner

Marc Dahn <dahn(at)tfiu(dot)de> writes:
> So -- is there a way to let the planner look through the CASE?

No. It would actually be wrong to simplify the expression in the way
you're imagining, since "1 = case when bool_expr then 1 else 0 end"
does not give the same result as the plain bool_expr if the latter
yields null.

If you're sufficiently desperate, though, you might consider some hack
like this:

regression=# CREATE OR REPLACE FUNCTION b_hasword(haystack TEXT, needle TEXT)
RETURNS boolean AS $func$
SELECT to_tsvector('english', $1) @@ plainto_tsquery($2)
$func$ LANGUAGE SQL STABLE;
CREATE FUNCTION
regression=# create function inteqbool(int,bool) returns bool as
regression-# $$select $1::bool = $2 $$ language sql stable;
CREATE FUNCTION
regression=# CREATE OPERATOR = (procedure = inteqbool, leftarg=int, rightarg=bool);
CREATE OPERATOR
regression=# explain select * from resource where 1=b_hasword(res_title, 'optical');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on resource (cost=4.20..14.38 rows=7 width=32)
Recheck Cond: (to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text))
-> Bitmap Index Scan on resource_to_tsvector_idx (cost=0.00..4.20 rows=7 width=0)
Index Cond: (to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text))
(4 rows)

which relies on the fact that the planner *will* simplify "boolexpr = true"
to just "boolexpr", so that after a couple of rounds of inlining and
simplification we get to just the bare @@ expression. But aside from
the time spent doing that, this approach could have unpleasant side
effects in the form of causing "int = bool" expressions to be accepted
generally, thus masking errors. Might be better to fix your client-side
code.

regards, tom lane