设置变量时，对记录的IS NOT NULL测试不会返回TRUE [英] IS NOT NULL test for a record does not return TRUE when variable is set
问题描述
使用plpgsql过程提取一条记录(如果存在)，然后提取一条记录.
Using a plpgsql procedure to extract a record if it exists, and then if it does, do something with it.
变量是行类型:
my_var my_table%rowtype;
我用一条SQL语句填充它:
I populate it with a SQL statement:
select * from my_table where owner_id = 6 into my_var;
我知道它肯定有一行:
raise notice 'my_var is %', my_var;
返回:
NOTICE: my_var is (383,6,10)
但是现在我想测试一下，如果条件失败，它是否具有这些记录和所有记录:
But now I want to test that it got the record and BOTH of these if conditions fail:
if my_var is null then
raise notice 'IT IS NULL';
end if;
if my_var is not null then
raise notice 'IT IS NOT NULL';
end if;
这些加薪都没有出现在我的消息日志中-它从未进入过障碍区.测试您是否从SELECT * INTO收到行的正确方法是什么?
Neither of these raises appear in my messages log - it just never enters the blocks. What's the correct way to test if you received a row from a SELECT * INTO?
推荐答案
我看到两个可能的原因，为什么...
I see two possible reasons, why ...
这些加薪都没有出现在我的消息日志中
Neither of these raises appear in my messages log
未登录
首先，通常不会使用默认设置将NOTICE写入数据库日志.我引用此处的手册:
Not logged
Firstly, a NOTICE is not normally written to the database log with default settings. I quote the manual here:
log_min_messages(enum)
控制将哪些消息级别写入服务器日志.有效值为DEBUG5，DEBUG4，DEBUG3，DEBUG2，DEBUG1，INFO，NOTICE，WARNING，ERROR，LOG，FATAL和
默认值为警告.请注意，此处LOG的排名与client_min_messages中的排名不同.
Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. (...)
The default is WARNING. Note that LOG has a different rank here than in client_min_messages.
强调粗体.还要注意client_min_messages(手册中的上一项)的不同默认值(NOTICE).
Bold emphasis mine. Also note the different default (NOTICE) for client_min_messages (previous item in the manual).
第二，考虑如何计算行表达式.如果(且仅当)每个单个元素为NULL时，测试row_variable IS NULL返回TRUE.给出以下示例:
Secondly, consider how a row expression is evaluated. A test row_variable IS NULL returns TRUE if (and only if) every single element is NULL. Given the following example:
SELECT (1, NULL) IS NULL AS a -- FALSE
,(1, NULL) IS NOT NULL AS b -- also FALSE
两个表达式均返回FALSE.换句话说，行(或记录)变量(1, NULL)既不是NULL，也不是NOT NULL.因此，您的两个测试均失败.
Both expressions return FALSE. In other words, a row (or record) variable (1, NULL) is neither NULL, nor is it NOT NULL. Therefore, both of your tests fail.
-> SQLfiddle with more details.
在此相关答案的CHECK约束中，此行为的更多详细信息，解释，链接和可能的应用:
在一组列上的非空约束
More details, explanation, links and a possible application for this behavior in a CHECK constraint in this related answer:
NOT NULL constraint over a set of columns
您甚至可以为记录变量分配一个NULL(rec := NULL)，如果该类型是众所周知的行类型，则结果将使每个元素都为NULL.否则，我们将处理一个匿名记录，并且该结构是未定义的，因此您将无法访问任何元素.但这不是像您的示例中的rowtype那样的情况(这是众所周知的).
You can even assign a record variable with NULL (rec := NULL), which results in every element being NULL - if the type is a well-known row type. Otherwise, we are dealing with an anonymous record and the structure is undefined and you cannot access elements to begin with. But that's not the case with a rowtype like in your example (which is always well-known).
测试您是否收到来自
SELECT * INTO的行的正确方法是什么?
What's the correct way to test if you received a row from a
SELECT * INTO?
您必须考虑即使该行已分配，该行也可能为NULL.该查询很可能已经返回了一堆NULL值(如果查询中的表定义允许NULL值).这样的测试在设计上将是不可靠的.
You have to consider that the row could be NULL, even if it was assigned. The query could very well have returned a bunch of NULL values (if the table definition in your query allows NULL values). Such a test would be unreliable by design.
有一种简单而安全的方法.使用GET DIAGNOSTICS ...或(如果适用)特殊变量FOUND:
There is a simple and secure approach. Use GET DIAGNOSTICS ... or (where applicable) the special variable FOUND:
SELECT * FROM my_table WHERE owner_id = 6 INTO my_var;
IF NOT FOUND THEN
RAISE NOTICE 'Query did not return a row!';
END IF;
这篇关于设置变量时，对记录的IS NOT NULL测试不会返回TRUE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
