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. > > -- > Thomas Munro > http://www.enterprisedb.com > Someone should include your explanation in the [fine] manual. Dane
