pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using for rec inside a function: behavior very slow
2+ messages / 2 participants
[nested] [flat]

* using for rec inside a function: behavior very slow
@ 2001-04-02 22:10  Jie Liang <jliang@ipinc.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Jie Liang @ 2001-04-02 22:10 UTC (permalink / raw)
  To: edipoelder@ig.com.br; +Cc: pgsql-sql


I have a function:
CREATE FUNCTION hasdup(text) RETURNS int4 AS '
declare
   v_id         int4;
   rat1         text;
   rat2         text;
   v_url        text;
   rec          record;

begin
     select id into v_id from urlinfo where url = $1;
     if NOT FOUND then
        return -1;
     end if;
     select codestr(v_id) into rat1;
     v_url:= $1||''%'';                
     for rec in select id,url from urlinfo where url like v_url order by
url loop
        raise notice ''%'',rec.url;
           select codestr(rec.id) into rat2;
           if rec.id <> v_id and rat1 = rat2 then
              update urlinfo set list = 1 where id = rec.id;
              return rec.id; 
           end if;
     end loop; 
     return 0;
end; 
' LANGUAGE 'plpgsql';


'where url like clause' is very slow in inside the function,
but when I directly use this statement in SQL, it is very quick,
is any quick way to return match:
where field like 'something%' inside the plsql function??


Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: using for rec inside a function: behavior very slow
@ 2001-04-03 01:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jie Liang <jliang@ipinc.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-04-03 01:16 UTC (permalink / raw)
  To: Jie Liang <jliang@ipinc.com>; +Cc: edipoelder@ig.com.br; pgsql-sql

Jie Liang <jliang@ipinc.com> 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-04-03 01:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-02 22:10 using for rec inside a function: behavior very slow Jie Liang <jliang@ipinc.com>
2001-04-03 01:16 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox