Обсуждение: Problems with plpgsql and FOR loops

I've got a bit of a strange problem that's causing me some MAJOR
headaches.

I'm developing the server-side of a large database application in
PostgreSQL.  This consists of a C daemon, and a LOT of stored
functions in the database.

I'm developing this in conjunction with another company, who is
developing the the client side.  I've got a 7.4 server that I'm
developing on, and once a day I push my changes up to a common
server that the client developers can use to test and work with.

That server is the problem, it's running 7.3.4 and I'm not in a
position to upgrade it in the near future.

Some of the plpgsql functions I've created work fine on 7.4, but
fail on 7.3.4.  Specifically, when I use a "FOR var IN select LOOP"
loop with a LONG select statement, it works fine on 7.4, but
bonks with "missing .. at end of SQL expression" in 7.3.4

I know the correct solution is to use the same version to develop
on that I'm using to test.  So I'm going to downgrade my version
to 7.3.4 for now ... but this doesn't solve my biggest problem:
getting the FOR loop to work.  It appears from the error that
the parser is getting confused between a FOR IN SELECT loop and
a FOR integer loop.

Does anyone have any suggestions on how to convince the parser
to interpret the loop correctly?  This is a MAJOR holdup for
me right now, and I'm on a tight schedule, so any help will be
a life-saver!

I've been unable to subscribe to the list, so please include me
in your reply directly.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Dear Bill Moran  ,

> I know the correct solution is to use the same version to develop
> on that I'm using to test.  So I'm going to downgrade my version
> to 7.3.4 for now ... but this doesn't solve my biggest problem:
> getting the FOR loop to work.  It appears from the error that
> the parser is getting confused between a FOR IN SELECT loop and
> a FOR integer loop.

If its not top secret we would like to see the code

--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: vishalkashyap@jabber.org
ICQ :      264360076
-----------------------------------------------
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---------------
I am usually called as Vishal Kashyap
and my Girlfriend calls me Vishal CASH UP.
Because everyone loves me as Vishal Kashyap
and my Girlfriend loves me as CASH.
                    ___
                   //\\\
                  ( 0_0 )
----------------o0o-----o0o---------------------

Bill Moran <wmoran@potentialtech.com> writes:
> Some of the plpgsql functions I've created work fine on 7.4, but
> fail on 7.3.4.  Specifically, when I use a "FOR var IN select LOOP"
> loop with a LONG select statement, it works fine on 7.4, but
> bonks with "missing .. at end of SQL expression" in 7.3.4

That error message suggests that plpgsql thinks the "var" is not
declared as a record or rowtype variable.  I think you should look
to the variable declaration as the source of the issue, not the FOR
statement itself.  There were some 7.4 fixes associated with
plpgsql rowtype variables, for instance this one:

2003-04-27 18:21  tgl

    * doc/src/sgml/plpgsql.sgml, src/pl/plpgsql/src/gram.y,
    src/pl/plpgsql/src/pl_comp.c, src/pl/plpgsql/src/plpgsql.h: Fix
    plpgsql so that variables of composite types (rowtypes) can be
    declared without having to write %ROWTYPE.  If the declared type of
    a variable is a composite type, it'll be taken to be a row variable
    automatically.

although my recollection is that without %ROWTYPE, 7.3 plpgsql would
fail on the variable declaration.

            regards, tom lane

V i s h a l Kashyap @ [Sai Hertz And Control Systems] wrote:
> Dear Bill Moran  ,
>
>> I know the correct solution is to use the same version to develop
>> on that I'm using to test.  So I'm going to downgrade my version
>> to 7.3.4 for now ... but this doesn't solve my biggest problem:
>> getting the FOR loop to work.  It appears from the error that
>> the parser is getting confused between a FOR IN SELECT loop and
>> a FOR integer loop.
>
> If its not top secret we would like to see the code

I have tried this with dozens of FOR loops and get the same problem
over and over again.  Here is 1 example, I could easily provide a
dozen more.  Keep in mind that this code works perfectly in Postgres
7.4.

    FOR rval IN
                 SELECT distinct 1 as Type,
                    OS.ID as ID,
                     trim(trailing FROM CAST(os.name AS char(85))) ||
                     ''  ('' || CAST((SELECT
                                         min(Series_Element.Delivery_Date)
                                         FROM    Series_Element
                                         WHERE    OS_ID = OS.ID
                                     ) AS CHAR(10)) ||
                     '')'' as DisplayName,
                     NULL AS OrderByName,
                     (SELECT    min(Series_Element.Delivery_Date)
                         FROM    Series_Element
                         WHERE    OS_ID = OS.ID
                     ) as IssueDate
                 FROM    OS_Issue OS
                 WHERE   exists
                     (SELECT    1
                         FROM     Series_element SE,
                                 Series S
                         WHERE    SE.OS_ID = OS.ID
                             AND S.ID = SE.Series_ID
                             AND    S.Issuer_ID = issuer
                             AND Delivery_Date between startdate and enddate
                     )
                 ORDER BY IssueDate DESC, DisplayName DESC
             LOOP

                     RETURN NEXT rval;

             END LOOP;

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Tom Lane wrote:
> Bill Moran <wmoran@potentialtech.com> writes:
>
>>Some of the plpgsql functions I've created work fine on 7.4, but
>>fail on 7.3.4.  Specifically, when I use a "FOR var IN select LOOP"
>>loop with a LONG select statement, it works fine on 7.4, but
>>bonks with "missing .. at end of SQL expression" in 7.3.4
>
> That error message suggests that plpgsql thinks the "var" is not
> declared as a record or rowtype variable.  I think you should look
> to the variable declaration as the source of the issue, not the FOR
> statement itself.  There were some 7.4 fixes associated with
> plpgsql rowtype variables, for instance this one:

Ahh ... this is an interesting twist.

In the cases where I'm having problems, I'm defining a custom type
with CREATE TYPE, because it's the easiest way I can think of to
move the data around.

Assuming:

CREATE TYPE testtype AS ...

Are you suggesting that:

rval testtype%ROWTYPE;

is likely to work around the problem?  So far, I've just been
using:

rval testtype;

I'll test this to see what happens.

> 2003-04-27 18:21  tgl
>
>     * doc/src/sgml/plpgsql.sgml, src/pl/plpgsql/src/gram.y,
>     src/pl/plpgsql/src/pl_comp.c, src/pl/plpgsql/src/plpgsql.h: Fix
>     plpgsql so that variables of composite types (rowtypes) can be
>     declared without having to write %ROWTYPE.  If the declared type of
>     a variable is a composite type, it'll be taken to be a row variable
>     automatically.
>
> although my recollection is that without %ROWTYPE, 7.3 plpgsql would
> fail on the variable declaration.
>
>             regards, tom lane
>


--
Bill Moran
Potential Technologies
http://www.potentialtech.com