Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f331H6K75695 for ; Mon, 2 Apr 2001 21:17:07 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f331Gvw24887; Mon, 2 Apr 2001 21:16:57 -0400 (EDT) To: Jie Liang cc: edipoelder@ig.com.br, pgsql-sql@postgresql.org Subject: Re: using for rec inside a function: behavior very slow In-reply-to: References: Comments: In-reply-to Jie Liang message dated "Mon, 02 Apr 2001 15:10:40 -0700" Date: Mon, 02 Apr 2001 21:16:56 -0400 Message-ID: <24884.986260616@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/19 X-Sequence-Number: 2358 Jie Liang writes: > v_url:= $1||''%''; > for rec in select id,url from urlinfo where url like v_url order by > url loop [ is slow ] LIKE index optimization doesn't happen if the LIKE pattern is a variable when the plan is created. In 7.1 you can work around this problem by using plpgsql's FOR ... EXECUTE notation, but I don't think there's any good answer in 7.0. for rec in execute ''select id,url from urlinfo where url like ''||quote_literal(v_url)||'' order by url'' loop regards, tom lane