当设置变量时，记录的非空测试是否返回TRUE

Using a plpgsql procedure to extract a record if it exists, and then if it does, do something with it.

使用plpgsql过程来提取一个记录，如果它存在的话，然后如果它确实存在，那么就用它来做一些事情。

The variable is a rowtype:

变量是一个行类型:

my_var my_table%rowtype;

I populate it with a SQL statement:

我用SQL语句填充它:

select * from my_table where owner_id = 6 into my_var;

I know it definitely has the row:

我知道它肯定有争议:

raise notice 'my_var is %', my_var;

Returns:

返回:

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;

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?

这些加薪都不会出现在我的邮件日志中——它只是永远不会进入区块。如果您从SELECT *接收到一行，正确的测试方法是什么?

1 个解决方案

#1

I see two possible reasons, why ...

我看到两个可能的原因，为什么……

Neither of these raises appear in my messages log

在我的消息日志中没有出现这些提升。

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)

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

控制将哪些消息级别写入服务器日志。有效值为DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、通知、警告、错误、日志、致命和恐慌。(…)默认是警告。注意，这里的日志与client_min_messages的级别不同。

Bold emphasis mine. Also note the different default (NOTICE) for client_min_messages (previous item in the manual).

我大胆的重点。还要注意client_min_messages的不同默认(注意)(手册中的前一项)。

Invalid test

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:

其次，考虑如何计算行表达式。如果(且仅当)每个元素为空时，测试row_variable为NULL。给定下面的例子:

SELECT (1, NULL) IS NULL AS a     -- FALSE
      ,(1, NULL) IS NOT NULL AS b -- also FALSE

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.

两个表达式返回FALSE。换句话说，行(或记录)变量(1,NULL)既不是NULL，也不是NULL。因此，您的两个测试都失败了。

-> SQLfiddle with more details.

-> SQLfiddle是什么?

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

在这个相关答案中的检查约束中，关于此行为的更多细节、解释、链接和一个可能的应用程序:对一组列的非空约束

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

您甚至可以为记录变量分配NULL (rec:= NULL)，这将导致每个元素都为NULL——如果类型是众所周知的行类型的话。否则，我们将处理一个匿名记录，并且该结构是未定义的，并且您不能首先访问元素。但是，在您的示例中(始终是众所周知的)的行类型不是这样的。

Solution: FOUND

What's the correct way to test if you received a row from a SELECT * INTO?

如果您从SELECT *接收到一行，正确的测试方法是什么?

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.

你必须考虑行可以是空的，即使它被赋值了。查询很可能已经返回了一堆空值(如果查询中的表定义允许空值)。这样的测试在设计上是不可靠的。

There is a simple and secure approach. Use GET DIAGNOSTICS ... or (where applicable) the special variable FOUND:

有一种简单而安全的方法。使用GET诊断…或(如适用)发现的特殊变量:

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;

Details in the manual.

详细的手册。