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. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
