Search Postgresql Archives

Re: A question about PL/pgSQL DECLAREd variable behavior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

--
Thomas Munro
http://www.enterprisedb.com
Someone should include your explanation in the [fine] manual.

Dane

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux