Re: A question about PL/pgSQL DECLAREd variable behavior - Mailing list pgsql-general

On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie@gmail.com> wrote:
> On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>>
>> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@gmail.com> wrote:
>> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com>
>> > wrote:
>> >> On 10/21/15 9:32 PM, Dane Foster wrote:
>> >>>
>> >>>     "If STRICT is not specified in the INTO clause, then target will
>> >>> be
>> >>>     set to the first row returned by the query, or to nulls if the
>> >>> query
>> >>>     returned no rows."
>> >>>
>> >>> Foot removed from mouth.
>> >>
>> >> Note however that there's some unexpected things when checking whether
>> >> a
>> >> record variable IS (NOT) NULL. It's not as simple as 'has the variable
>> >> been
>> >> set or not'.
>> >
>> > Please elaborate. I'm entirely new to PL/pgSQL so the more details you
>> > can
>> > provide the better.
>> > Thanks,
>>
>> The surprising thing here, required by the standard, is that this
>> expression is true:
>>
>>   ROW(NULL, NULL) IS NULL
>>
>> So "r IS NULL" is not a totally reliable way to check if your row
>> variable was set or not by the SELECT INTO, if there is any chance
>> that r is a record full of NULL.  "r IS NOT DISTINCT FROM NULL" would
>> work though, because it's only IS [NOT] NULL that has that strange
>> special case.  Other constructs that have special behaviour for NULL
>> don't consider a composite type composed of NULLs to be NULL.  For
>> example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.
>

> Someone should include your explanation in the [fine] manual.

The quirky standard behaviour of IS [NOT] NULL with rows is described
in a 'Note' section here:

http://www.postgresql.org/docs/9.4/static/functions-comparison.html

But I do think we should consider pointing out explicitly that "IS
NULL" doesn't mean the same thing as, erm, "is null" where it appears
throughout the documentation, and I proposed a minor tweak:

http://www.postgresql.org/message-id/CAEepm=1wW4MGBS6Hwteu6B-OMZiX6_FM=Wfyn7oTeHyCfkgDDw@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com