Re: A question about PL/pgSQL DECLAREd variable behavior

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.

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