By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,161 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,161 IT Pros & Developers. It's quick & easy.

Problems with plpgsql and FOR loops

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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: vi***********@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---------------------
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a
Bill Moran <wm****@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #4

P: n/a
Tom Lane wrote:
Bill Moran <wm****@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.