Re: A question about PL/pgSQL DECLAREd variable behavior

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.

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