Re: [GENERAL] Problems with plpgsql and FOR loops
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: [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems with plpgsql and FOR loops
Bill Moran [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 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)
FROMSeries_Element
WHERE OS_ID = OS.ID
) AS CHAR(10)) ||
'')'' as DisplayName,
NULL AS OrderByName,
(SELECT min(Series_Element.Delivery_Date)
FROMSeries_Element
WHERE OS_ID = OS.ID
) as IssueDate
FROMOS_Issue OS
WHERE exists
(SELECT 1
FROMSeries_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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Problems with plpgsql and FOR loops
Tom Lane wrote: Bill Moran [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
