Skip site navigation (1) Skip section navigation (2)

Re: Null row vs. row of nulls in plpgsql

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Greg Stark" <greg(dot)stark(at)enterprisedb(dot)com>, pgsql-hackers(at)postgresql(dot)org, "Oleg Serov" <serovov(at)gmail(dot)com>
Subject: Re: Null row vs. row of nulls in plpgsql
Date: 2008-09-29 07:55:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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;

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:

In response to

pgsql-hackers by date

Next:From: Stephen FrostDate: 2008-09-29 09:38:57
Subject: Re: Proposal: move column defaults into pg_attributealong with attacl
Previous:From: Markus WannerDate: 2008-09-29 07:05:00
Subject: Re: Proposal: move column defaults into pg_attribute along with attacl

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group