Обсуждение: Fwd: Calling functions inside a function: behavior

Edipo,
> FUNCTION (...) AS '(...)BEGIN 
> PERFORM FUNCTION1(); 
> PERFORM FUNCTION2(); 
> (...) 
> END;' LANGUAGE 'PLPGSQL'; 

I'm not sure about that syntax (PERFORM).  I ususally set my functions
equal to a value ('remote_result := Function1(paramater)').  This has
the added advantage of letting me use an exit value from my sensted
function to communicate with the parent function ('IF remote_result =
'ERROR' THEN ... ').  I'm just not sure what bugaboos you may be running
into with PERFORM.

One possibility (Tom, Jan, verify me on this):  All calls in a function
are automatically nested in a transaction.  Thus, if FunctionM calls
Function1, 2, and 3, then the system should reverse Functions 1, 2, and
3 if M errors out at any point.  This means that all of the changes made
by the nested calls need to be cached somehow; on a slow or low-memory
system, this could lead to bogging down as your machine utilizes its
swap space if Functions 1, 2, and 3 involve heavy data interactions.

One way to test this, is to modify your test script as follows:

BEGIN WORK;
PERFORM Function1;
SELECT current_timestamp;
PERFORM Function2;
SELECT current_timestamp;
etc ...
COMMIT WORK;

If the test script bogs down as well, you have your answer although the
workaround may be tricky to implement.

-Josh Berkus




______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Em 02 Apr 2001, Josh Berkus escreveu: 

>BEGIN WORK; 
>COMMIT WORK; 
   In time... I think that could be great if postgresql implement a commit 
inside functions. 
   Abracos, 
   Edipo Elder    [edipoelder@ig.com.br] 

_________________________________________________________
Oi! Voc� quer um iG-mail gratuito?
Ent�o clique aqui: http://www.ig.com.br/paginas/assineigmail.html

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_urlorder 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

I tested select statement inside sql and plpgsql function,
very slow


CREATE FUNCTION geturllike(text) RETURNS SETOF text AS '
SELECT url as url FROM urlinfo WHERE url LIKE $1;
'LANGUAGE 'sql';

CREATE FUNCTION hasdup(text) RETURNS int4 AS '
declare
   v_id         int4;
   rat1         text;
   rat2         text;
   v_url        text;
   rec          record;

begin
     v_url:= $1||''%'';

     for rec in select id,url from urlinfo where url like v_url order by
url loop
        raise notice ''%'',rec.url;
     end loop;
     return 0;
end;
' LANGUAGE 'plpgsql';

Why so slow????
Is it a bug??


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

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