Thanks for the reply. (i'm sorry for that i didn't really know how to reply to a certain message...)
well, i used LIKE, but i actually wanted just "starts with". the solution i found without using LIKE is this: CREATE OR REPLACE FUNCTION test_func(STR text) RETURNS integer AS $BODY$ declare STR2 varchar; begin -- example: if STR is 'abc' then STR2 would be 'abd' STR2 := substring(STR,0,length(STR))||chr((ascii(substring(STR,length(STR)))+1)); insert into plcbug(val) values('begin time before perform'); perform t1.val FROM t1 WHERE (COALESCE(rpad((val)::text, 100, ' '::text), ''::text) ~>=~ STR::text) AND (COALESCE(rpad((val)::text, 100, ' '::text), ''::text) ~<~ STR2::text) order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5; insert into plcbug(val) values('time after perform'); return 1; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION test_func(text) OWNER TO postgres; 1. is there any more elegant solution? 2. considering LIKE, practically there are only two cases: the expression (variable||'%') may be '%something%' or 'something%' [*], right?? do you think the optimizer can do better by conditionally splitting the plan according to actual value of a variable? [*] for the sake of the discussion lets forget about '_something'. Thanks again. On Tue, Aug 16, 2011 at 16:40, Tom Lane <t...@sss.pgh.pa.us> wrote: > Eyal Wilde <e...@impactsoft.co.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 >