Обсуждение: plpgsql For SQLQuery Loop Flags Error

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/current/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-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

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.
Thismay 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



>>
     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 ;
>>

Puneet Paul <paulptech@yahoo.com> writes:
>  DECLARE
>      curTrackList char(15) ALIAS for $1;
>     sliceFile varchar ALIAS for $2;
>     lmfpLimit integer ALIAS for $3
>     mTrackDet RECORD;

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

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


> I get error "missing ".." at end of SQL expression"
...
> Can someone help please

The problems appear to be in your declare block:

>  curTrackList char(15) ALIAS for $1;
>  sliceFile varchar ALIAS for $2;
>  lmfpLimit integer ALIAS for $3

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@turnstep.com
PGP Key: 0x14964AC8 200312182010

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

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