pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing 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