Re: When is a record NULL?

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: When is a record NULL?
Date: 2009-07-26 22:46:19
Message-ID: 69F6A881-08D2-4C94-A925-A4BA2820DBDA@kineticode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Eric B. Ridge 2009-07-26 23:02:53 Re: When is a record NULL?
Previous Message Sam Mason 2009-07-26 22:24:14 Re: When is a record NULL?