From: | Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> |
---|---|
To: | Dane Foster <studdugie(at)gmail(dot)com> |
Cc: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: A question about PL/pgSQL DECLAREd variable behavior |
Date: | 2015-10-23 01:15:31 |
Message-ID: | CAEepm=1Z5OpdkYV8+TkztCUR=wMt_jhOXTKh223weKQPqvN=wQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:
> On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>>
>> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie(at)gmail(dot)com> wrote:
>> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)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:
--
Thomas Munro
http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tim van der Linden | 2015-10-23 01:48:10 | Re: Multiple word synonyms (maybe?) |
Previous Message | Dane Foster | 2015-10-22 23:33:35 | Re: A question about PL/pgSQL DECLAREd variable behavior |