Re: PL/pgSQL loops?

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Johnny Jrgensen" <pgsql(at)halfahead(dot)dk>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: PL/pgSQL loops?
Date: 2001-11-29 16:09:48
Message-ID: 20011129073009.E43179-100000@megazone23.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


On Thu, 29 Nov 2001, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > Something like the below seemed to make it work for me under
> > 7.2b3:
>
> Oh ... duh! I've been bit by that before myself. In the original,
> since the FOR loops were using integer variables (not record or rowtype
> variables as they should've), the plpgsql parser was expecting an
> integer FOR-loop. Which goes like
>
> FOR ivar IN expression .. expression LOOP stmts END LOOP;
>
> Evidently, somewhere around the END LOOP it realized that it was missing
> the .. part of the construct.
>
> I've mumbled before that it's bad form to be using the declared type
> of the FOR variable to drive the syntaxing of FOR loops --- poor error
> recovery and unhelpful error messages are exactly the reasons why.
> Not sure that this can easily be fixed, however.

That is wierd. I think I may see what's happening though.

ISTM, "fori_lower" in plpgsql's gram.y keeps going until it finds a .. or
semicolon. Then the next expression keeps going until it finds LOOP
or a semicolon. In the original example this means the outer's FOR
expressions seem to be (with some help from elog(notice))
the select id through the semicolon on the first update and then
end loop as the second. At which point it seems to bomb on the semicolon.

In addition, this seems to work:
for intvar in 1 .. (select max(a) from a) LOOP
and this doesn't:
for intvar in 1 .. (select max(loop) from a) LOOP
and this does:
for intvar in (select max(loop) from a) .. 1 LOOP

I'm guessing that plpgsql doesn't want to try to determine if
an expression is valid and so just concats stuff to pass off,
but that seems to mean that you need to double quote loop in
the second (which is somewhat unobvious). You don't need to
in the third because fori_lower doesn't stop when it sees
a LOOP.

A quick hack for the case where you have a FOR intvar IN
<single expression> LOOP cases where you meant FOR record
IN may be to make fori_lower stop when it sees a LOOP and
error (there's an equivalent error for running off the
end of the function) at which point we can probably give
a meaningful error message.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Esger Abbink 2001-11-29 16:56:30 need some help understanding sloq query
Previous Message Haller Christoph 2001-11-29 15:58:33 Re: Check for table existence