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

Hi,
Thank you very much for your answer and your time. I found on
https://www.postgresql.org/docs/9.1/static/functions-comparison.html that

> Because of this behavior, IS NULL and IS NOT NULL do not always return
inverse results for row-valued expressions; in particular, a row-valued
expression that contains both null and non-null fields will return false
for both tests.

It explains my "strange" results.
Thanks again!

Manuel

Le 24 avr. 2017 7:19 PM, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> a
écrit :

> 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.
>
>