Thread: ROWTYPE initialization question
'lo list,
I have a plpgsql SP where I loop through a cursor. I have an internal
variable that keeps the previous row, so that I can compare it with the
current row in the cursor.
Like so;
DECLARE
current table%ROWTYPE;
previous table%ROWTYPE;
BEGIN
LOOP
FETCH tableCur INTO current;
-- Do stuff
But, in this loop I need to compare the previous row to the current one.
To do that I need to know whether a row was assigned to 'previous', or
there'll be very little to compare (if it doesn't throw an error).
How do I check for that?
I guess it would be like this, but I'd like to be sure.
IF previous IS NOT NULL
THEN
-- Compare previous and current column values
END IF
previous := current;
END LOOP;
END;
Thanks in advance,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On Thu, Nov 09, 2006 at 04:37:23PM +0100, Alban Hertroys wrote: > 'lo list, > > I have a plpgsql SP where I loop through a cursor. I have an internal > variable that keeps the previous row, so that I can compare it with the > current row in the cursor. > Like so; > > DECLARE > current table%ROWTYPE; > previous table%ROWTYPE; > BEGIN > LOOP > FETCH tableCur INTO current; > > -- Do stuff > > But, in this loop I need to compare the previous row to the current one. > To do that I need to know whether a row was assigned to 'previous', or > there'll be very little to compare (if it doesn't throw an error). > How do I check for that? > > I guess it would be like this, but I'd like to be sure. > > IF previous IS NOT NULL > THEN > -- Compare previous and current column values > END IF > > previous := current; > END LOOP; > END; Try it and see. :) I think that will work. Might be better to just capture the error. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim C. Nasby wrote: > On Thu, Nov 09, 2006 at 04:37:23PM +0100, Alban Hertroys wrote: >> 'lo list, >> >> I have a plpgsql SP where I loop through a cursor. I have an internal >> variable that keeps the previous row, so that I can compare it with the >> current row in the cursor. >> Like so; >> >> DECLARE >> current table%ROWTYPE; >> previous table%ROWTYPE; >> BEGIN >> LOOP >> FETCH tableCur INTO current; >> >> -- Do stuff >> >> But, in this loop I need to compare the previous row to the current one. >> To do that I need to know whether a row was assigned to 'previous', or >> there'll be very little to compare (if it doesn't throw an error). >> How do I check for that? >> >> I guess it would be like this, but I'd like to be sure. >> >> IF previous IS NOT NULL >> THEN >> -- Compare previous and current column values >> END IF >> >> previous := current; >> END LOOP; >> END; > > Try it and see. :) I think that will work. > > Might be better to just capture the error. Reality caught up, I had to put this procedure in our development database or people wouldn't be able to continue their work. It seems to work. I suppose the real question is this: As it is not possible to initialize a %ROWTYPE type variable to NULL, is comparing it to NULL valid or is it comparing apples and oranges? Does it yield the expected result (true if the %ROWTYPE variable is undefined, false once it is defined)? It seems to work as is, but this part of PL/PgSQL seems to be a bit unspecific. It might as well have worked by using the FOUND special variable, or have yielded an error (which it didn't). Looks like I'll need to device some test cases to prove how the various subtleties behave. Now where did I put that can of time... -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
On Nov 15, 2006, at 2:07 AM, Alban Hertroys wrote: > I suppose the real question is this: As it is not possible to > initialize > a %ROWTYPE type variable to NULL, is comparing it to NULL valid or > is it > comparing apples and oranges? Does it yield the expected result > (true if > the %ROWTYPE variable is undefined, false once it is defined)? > > It seems to work as is, but this part of PL/PgSQL seems to be a bit > unspecific. It might as well have worked by using the FOUND special > variable, or have yielded an error (which it didn't). > > Looks like I'll need to device some test cases to prove how the > various > subtleties behave. Now where did I put that can of time... As far as I can tell from the code, checking a %ROWTYPE variable IS NULL is perfectly valid. I view it as a bug that you can't assign NULL to a %ROWTYPE variable. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)