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:
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)
if my_var is null then
raise notice 'IT IS NULL';
if my_var is not null then
raise notice 'IT IS NOT NULL';
SELECT * INTO
I see two possible reasons, why ...
Neither of these raises appear in my messages log
NOTICE is not normally written to the database log with default settings. I quote the manual here:
Controls which message levels are written to the server log. Valid values are
The default is WARNING. Note that
LOGhas a different rank here than in
Bold emphasis mine. Also note the different default (
client_min_messages (previous item in the manual).
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.
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).
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
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;