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
|
| Список | pgsql-bugs |
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:
Reading those the best solution is to simply compare for distinctness to null.
rec IS DISTINCT FROM NULL
David J.
В списке pgsql-bugs по дате отправления: