Thread: LOOP and EXIT

Hello,

I'm new here in this group... and I hope that I find the solution for my
problem here.

I wrote a simple function which looks like this:

CREATE OR REPLACE FUNCTION watchdog() RETURNS TIME AS '
 DECLARE
  watchdog_control RECORD;
  help RECORD;
 BEGIN
  LOOP
   PERFORM * FROM watchdog_ctl;

   IF NOT FOUND THEN
    EXIT;
   END IF;

   PERFORM sleep(3);

  END LOOP;
  RETURN now();
 END;
' LANGUAGE plpgsql;

That means that I want to leave the loop if there isn't an entry in the
table watchdog_ctl. This works fine if the table is empty at the startup of
the function (select watchdog();). But if I empty the table watchdog_ctl
while the function is running it seems to be an endless loop. Hay anyone of
you an idea why??

Many thanks!

Sebastian

On Tue, 12 Jul 2005, [iso-8859-1] Sebastian Kühner wrote:

> Hello,
>
> I'm new here in this group... and I hope that I find the solution for my
> problem here.
>
> I wrote a simple function which looks like this:
>
> CREATE OR REPLACE FUNCTION watchdog() RETURNS TIME AS '
>  DECLARE
>   watchdog_control RECORD;
>   help RECORD;
>  BEGIN
>   LOOP
>    PERFORM * FROM watchdog_ctl;
>
>    IF NOT FOUND THEN
>     EXIT;
>    END IF;
>
>    PERFORM sleep(3);
>
>   END LOOP;
>   RETURN now();
>  END;
> ' LANGUAGE plpgsql;
>
> That means that I want to leave the loop if there isn't an entry in the
> table watchdog_ctl. This works fine if the table is empty at the startup of
> the function (select watchdog();). But if I empty the table watchdog_ctl
> while the function is running it seems to be an endless loop. Hay anyone of
> you an idea why??

What version are you using?  I believe in versions prior to 8.0, that
perform is going to see the same snapshot each time, which means it's not
going to see the effects of your delete.  The function seems to act as you
want in 8.1beta (can't test 8.0 right now).