On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas.munro@xxxxxxxxxxxxxxxx> wrote:
> Someone should include your explanation in the [fine] manual.On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie@xxxxxxxxx> wrote:
> On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
> <thomas.munro@xxxxxxxxxxxxxxxx> wrote:
>>
>> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@xxxxxxxxx> wrote:
>> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx>
>> > 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.
>
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@xxxxxxxxxxxxxx
It just occurred to me that another option, for my specific example, would be to record/cache FOUND instead of testing the RECORD variable for its NULLness. Unless of course assigning FOUND to a variable is a pass-by-reference assignment, which in the actual code that I'm writing would be problematic because FOUND is set many times because there are at least 4 SQL commands that my function executes.
Dane
