Thread: 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
Re: Problems with plpgsql and FOR loops
From
"V i s h a l Kashyap @ [Sai Hertz And Control Systems]"
Date:
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