On Jul 25, 2009, at 4:41 PM, David E. Wheeler wrote:
Useless perhaps, but it's gonna happen, and someone may even have a
reason for it. Until such time as NULLs are killed off, we need to
be able to deal with SQL's pathologies.
And something I'd like to be able to handle in a while loop, as I'm
actually fetching one row at a time from two cursors and need to be
able to tell when I've reached the end of a cursor. This example
highlights the issue:
\set QUIET 1
SET client_min_messages = warning;
BEGIN;
CREATE TABLE peeps (
name TEXT NOT NULL,
dob date,
ssn text,
active boolean NOT NULL DEFAULT true
);
INSERT INTO peeps
VALUES ('Tom', '1963-03-23', '123-45-6789', true),
('Damian', NULL, NULL, true),
('Larry', NULL, '932-45-3456', true),
('Bruce', '1965-12-31', NULL, true);
CREATE TYPE dobssn AS ( dob date, ssn text );
CREATE FUNCTION using_loop() RETURNS SETOF dobssn LANGUAGE
plpgsql AS $$
DECLARE
stuff CURSOR FOR SELECT dob, ssn from peeps where active
ORDER BY name;
BEGIN
FOR rec IN stuff LOOP
RETURN NEXT rec;
END LOOP;
END;
$$;
CREATE FUNCTION using_while() RETURNS SETOF dobssn LANGUAGE
plpgsql AS $$
DECLARE
stuff CURSOR FOR SELECT dob, ssn from peeps where active
ORDER BY name;
rec dobssn;
BEGIN
open stuff;
FETCH stuff INTO rec;
WHILE NOT rec IS NULL LOOP
RETURN NEXT rec;
FETCH stuff INTO rec;
END LOOP;
END;
$$;
SELECT * FROM using_loop();
SELECT * FROM using_while();
ROLLBACK;
Output:
dob | ssn
------------+-------------
1965-12-31 |
|
| 932-45-3456
1963-03-23 | 123-45-6789
(4 rows)
dob | ssn
------------+-----
1965-12-31 |
(1 row)
So somehow the use of the loop to go right through the cursor can tell
the difference between a record that's all nulls and the when the end
of the cursor has been reached. My use of the while loop, however,
cannot tell the difference, and AFAICT, there is no way to detect the
difference in SQL. Is that correct? Is there some way to get
using_while() to properly return all the records?
FYI, using:
WHILE rec IS DISTINCT FROM NULL LOOP
Results in an infinite loop. So does:
WHILE NOT rec IS NOT DISTINCT FROM NULL LOOP
And this, of course:
WHILE rec IS NOT NULL LOOP
Returns no rows at all.
Surely someone has run into this before, no?
Thanks,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
