Re: Null row vs. row of nulls in plpgsql

Greg Stark <greg(dot)stark(at)enterprisedb(dot)com> writes:
> On 27 Sep 2008, at 09:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> ISTM that the fundamental problem is that plpgsql doesn't distinguish
>> properly between a null row value (eg, "null::somerowtype") and a
>> row of null values (eg, "row(null,null,...)::somerowtype"). When that
>> code was designed, our main SQL engine was pretty fuzzy about the
>> difference too, but now there is a clear semantic distinction.

> Iirc the reason for this fuzziness came from the SQL spec definition
> of IS NULL for rows. As long as you maintain that level of spec-
> compliance I don't think there are any other important constraints on
> pg behaviour.

I started to poke into this and found out that it was a bit subtler than
I thought. It'd be possible to associate a "rowisnull" state value
with a row variable, but the problem is that plpgsql treats the row
fields as independent variables that can be accessed without touching
the row. In particular you can assign null or nonnull values to
individual fields. So consider

-- presumably, this'll set rowisnull to TRUE:
rowvar := NULL;
-- this had better cause rowisnull to become FALSE:
rowvar.field1 := 42;
-- does this cause it to become TRUE again?
rowvar.field1 := NULL;

There are a bunch of implementation problems with making any such
behavior happen, since the row field variables don't currently "know"
that they are members of a row, and indeed it's possible for the same
variable to be a member of more than one row. But the core issue is
that this interaction seems to fuzz the distinction between "row is
null" and "all the row's elements are null". In particular, if you
think that rowisnull should be TRUE after the above sequence, then
I think you are saying they are the same thing. So maybe the spec
authors are smarter than we are.

Thoughts? What would a consistent behavior look like?

regards, tom lane