Re: Handling NULL records in plpgsql

On Oct 24, 2008, at 7:19 PM, Tom Lane wrote:
> Decibel! <decibel@decibel.org> writes:
>> Was anything ever done with http://archives.postgresql.org/pgsql- 
>> hackers/2008-09/msg01758.php ?
>
> No, we got stalled on what the behavior really ought to be:
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01772.php
>
>> I have a different issue I'm seeing, but I think it's related, and
>> ISTM it's a bug (on 8.2.10):
>
> It's entirely possible for a row variable to be in a state where  
> neither
> IS NULL nor IS NOT NULL is true.  RTFM (under Comparison Operators) or
> see the SQL spec.

Ahh, I finally saw the bit that talks about it.

I really think we should have a way of telling if a array/row/record  
variable is actually set to something, and I'm pretty sure that  
should be unrelated to whether all the elements in it happen to be  
null. And the IS NOT NULL case seems exceptionally broken. I think  
it's extremely confusing to have it behave differently than NOT blah  
IS NULL.

This puts us in an ugly position. Do we break with spec? Or should we  
come up with a different construct (IS [NOT] DEFINED?)? I'm  
disinclined to just leave it as-is, because I think it's pretty  
common for people to want to see if a variable is set or not. I'm  
inclined towards DEFINED, as ugly as it is, so that we're not  
breaking the spec.

To answer the questions in that thread, I would say that a record  
containing all nulls is still a distinct record. It was set to  
something, it just happens that that something contained all nulls.  
That's definitely not the same as it being set to nothing. Consider:

CREATE TABLE moo(a int, b int, c int);
CREATE TABLE cow(LIKE moo);
INSERT INTO moo SELECT NULL, NULL, NULL FROM generate_series(1,10) i;
SELECT count(*) FROM moo;
SELECT count(*) FROM cow;

SELECT INTO rowvar_a * FROM moo LIMIT 1;
SELECT INTO rowvar_b * FROM cow LIMIT 1;

I would argue that rowvar_b IS NOT NULL should be false and rowvar_a  
IS NOT NULL should be true.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828