Re: A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com
> wrote:

> 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:
>
>
> http://www.postgresql.org/message-id/CAEepm=1wW4MGBS6Hwteu6B-OMZiX6_FM=Wfyn7oTeHyCfkgDDw@mail.gmail.com
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
​
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
​