On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie@xxxxxxxxx> wrote: > 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. > > 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@xxxxxxxxxxxxxx -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
