Hi All,

I am compiling a function that uses the For Query
loop....End Loop.

I get error "missing ".." at end of SQL expression"

I have read following Note: The PL/pgSQL parser
presently distinguishes the two kinds of FOR loops
(integer or query result) by checking whether the
target variable mentioned just after FOR has been
declared as a record or row variable. If not, it's
presumed to be an integer FOR loop. This can cause
rather nonintuitive error messages when the true
problem is, say, that one has misspelled the variable
name after the FOR. Typically the complaint will be
something like missing ".." at end of SQL expression

at
http://www.postgresql.org/docs/curre...RUCTURES-LOOPS
Re checked the RECORD variable in function
CREATE OR REPLACE FUNCTION slice(char(15),varchar,
integer) RETURNS integer AS '
DECLARE
curTrackList char(15) ALIAS for $1;
sliceFile varchar ALIAS for $2;
lmfpLimit integer ALIAS for $3
mTrackDet RECORD;

lpmfSum integer := 0;
Sliced CONSTANT integer := 2;
curId varchar;
counter integer :=1 ;
BEGIN

----Build the curSliceId value
curId := curTrackList;
Insert into mysliceDetail
values(''abcdef'',''Ghijkl'',0);

FOR mTrackDet IN SELECT myrequest.trackId,
lmfpsize from myrequest,track where trkfound <> Sliced
and myRequest.trackid = track.trackId and lmfpsize > 0
order by volatility LOOP

-- Now "mTrackDet" has one record from slice
list
--LOOP
--WHILE lpmfSum < lpmfLimit LOOP

--Insert into mysliceDetail values(curId,
mTrackDet.trackId ,0);
--Insert into curSliceDetail values(curId,
mTrackDet.trackId ,0);

--lmfpSum := mTrackDet.lmfpsize + lmfpSum ;
--EXIT WHEN lpmfSum > lpmfLimit
--END LOOP;
insert into sliceToBuild values(curId, 0);
copy curSliceDetail to sliceFile;
counter := counter + 1;
Truncate Table curSliceDetail;
---Generate next curSliceId
curSliceId := curTrackList + counter;
END LOOP;

RETURN 1;
END;
' LANGUAGE plpgsql;

Can someone help please

Regards
Paul


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

I should play with your code, but I'm lazy.
1) lmfpLimit integer ALIAS for $3

is missing the trailing ";"
lmfpLimit integer ALIAS for $3;

2) VARCHAR variables need a length
curId varchar;
should be
curId varchar(100);

3) I have not seen this style of declaration, where the argument type is repeated from the function's argument list. This may be OK, but if nothing else helps maybe removing the type will help.
curTrackList char(15) ALIAS for $1;
sliceFile varchar ALIAS for $2;
lmfpLimit integer ALIAS for $3



sliceFile varchar ALIAS for $2;
lmfpLimit integer ALIAS for $3
mTrackDet RECORD;

lpmfSum integer := 0;
Sliced CONSTANT integer := 2;
curId varchar;
counter integer :=1 ;


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Puneet Paul <paulptech (AT) yahoo (DOT) com> writes:
If that's an accurate copy of your function, then the problem is likely
the lack of a semicolon on the lmfpLimit line. I'm not sure why you
don't get a syntax error, but evidently the "mTrackDet RECORD" is
getting treated as a noise phrase in the lmfpLimit declaration.

What Postgres version is this, anyway? The current sources do not look
like they'd accept such a thing without raising an error, but possibly
older releases would.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The problems appear to be in your declare block:

You need a semicolon after the #3, and if declaring a
variable as an alias, you do /not/ put in the data type
(because you already declared it). In other words:

curTrackList ALIAS fOR $1;
sliceFile ALIAS FOR $2;
lmfpLimit ALIAS FOR $3;

That may or may not be what is causing the error you saw:
plpgsql's error reporting is not always as helpful as
it should be. Just start at the top of the file and
work your way down, looking for easy stuff. Then cut things
out of the function until it works, and add things back in a
line at a time until you figure out the problem(s).

- --
Greg Sabino Mullane greg (AT) turnstep (DOT) com
PGP Key: 0x14964AC8 200312182010

-----BEGIN PGP SIGNATURE-----

iD8DBQE/4lDovJuQZxSWSsgRAulsAJ0bvUiXWiKmUDLV6esHoZtuQ6D5eQ Cgi5AA
xKH9t+TM59YbXa2dc7CyjRY=
=yapu
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html