Re: Null row vs. row of nulls in plpgsql

2008/9/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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;

this sequence is wrong. in SQL rowvar has same behave as pointer. When
you would to fill rowvar you should to call constructor first.

some like
rowvar := NULL; -- null value
rowvar := constructor(null);
rowvar := constructor();
rowvar.field = 42;

regards
Pavel Stehule

>
> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>