Re: plpgsql bug: `EXECUTE(sql_command) INTO rec` returns sometimes a NULL-able, sometimes not

On Mon, Apr 24, 2017 at 8:48 AM, Manuel Pradal <manuel(dot)pradal(at)gmail(dot)com>
wrote:

> Hi,
>
> Using PL/SQL language, I saw a strange behavior using
> "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.
> It seems that record content infers with existence test of whole record.
>
> You can see in attached file the possible bug in action.
>
> Should I use "IF NOT FOUND" syntax? Is it more reliable?
>
>
​tl/dr; rec IS DISTINCT FROM NULL​

​This does not seem like a bug.​

​You're reported what does happen but not what you expect to happen and why.

​As Pavel points out the docs for "Obtaining the Result Status" (pl/pgsql)
make an effort to point out:

"Other PL/pgSQL statements do not change the state of FOUND. Note in
particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not
change FOUND."

You could also try:

NOT (rec IS NULL)

which is the a better way to determine whether a composite record is
absent/present.

Even then that only works if at least one column of the record is
guaranteed to be not null. See the docs for more details:

https://www.postgresql.org/docs/9.6/static/functions-comparison.html

Reading those the best solution is to simply compare for distinctness to
null.

rec IS DISTINCT FROM NULL

David J.