Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
Дата
Msg-id CAKFQuwaAXimnehc5_D29pMgaadL3sq11VjXm5-QS=wxfvyXGXA@mail.gmail.com
обсуждение исходный текст
Ответ на [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returns sometimes aNULL-able, sometimes not  (Manuel Pradal <manuel.pradal@gmail.com>)
Ответы Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not  (Manuel Pradal <manuel.pradal@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Mon, Apr 24, 2017 at 8:48 AM, Manuel Pradal <manuel.pradal@gmail.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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Manuel Pradal
Дата:
Сообщение: Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
Следующее
От: Manuel Pradal
Дата:
Сообщение: Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not