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