On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote:
> On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdu...@gmail.com> wrote: > > On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro > > <thomas.mu...@enterprisedb.com> wrote: > >> > >> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdu...@gmail.com> > wrote: > >> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@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. > > > > 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=wfyn7otehycfkg...@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
