Postgres unassigned record, is there a way to test for null?

Question

Is there some way to test an unassigned record for null? (Sorry, sqlfiddle doesn't like my DO block.) Thanks.

DO
$$
DECLARE
r record;
BEGIN

r := null;

if r is null    -- ERROR:  record "r" is not assigned yet
then
end if;

END
$$;

Answer 1

The error can be avoided by writing:

   select null into r;

or alternatively:

   r:=row(null);

such that r gets initialized with a tuple structure.

Still, be aware that record variables are unintuitive in other of ways concerning NULLs, and more generally hard to work with outside of their base use case (cursor-style iterating).

Answer 2

If you wrap the test with an exception handler you can use the "not initialized" error to do the check for you.

DO $$
DECLARE
r record;
BEGIN

r := null;

  BEGIN
    IF r IS NOT NULL THEN
        raise notice 'R IS INITIALIZED';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
        raise notice 'R IS NOT INITIALIZED';
  END;

END
$$;

Answer 3

select *
from t
where id = p_id
into r;

if found then
...
else
...
end if;

https://www.solvingsoftware.dev/testing-for-an-empty-record-variable-in-postgresql/

Answer 4

I would couple that var with the bool variable and set bool variable whenever record is set:

DO
$$
DECLARE
r record;
rSet bool;
BEGIN    

if rSet then  -- it is false at the beginning

end if;

-- whenever r is set do this as well
r := select a,b;
rSet = true;

-- whenever that value was consumed, set it to "NULL" again:
return next r.a, r.b;
rSet = false;

END
$$;