Re: Problems with plpgsql and FOR loops

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