sql - IS NOT NULL test for a record does not return TRUE when variable is set

Question

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

The variable is a rowtype:

my_var my_table%rowtype;

I populate it with a SQL statement:

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?

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

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.

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

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:

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.

-> SQLfiddle with more details.

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

Solution: FOUND

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

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:

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.