Re: When is a record NULL?

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