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
$$;

Here is a fiddle. http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/825 You need to change the terminator from ";" so it doesn't get confused by the semicolons in the do block body. — Gary - Stand with Ukraine, Jun 11 '15 at 16:46

score 9 · Accepted Answer · answered Jun 14 '15 at 16:16

9

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

answered Jun 14 '15 at 16:16

Daniel Vérité

58,074
15
129
156

I never even though to try something like this. Thanks ! I'm actually trying to loop trough a set of data and split into two sets of data, so it is sort of like a cursor (I guess.) – Emery Lapinski Jun 15 '15 at 17:59

score 2 · Answer 2 · answered Jun 11 '15 at 16:56

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
$$;

answered Jun 11 '15 at 16:56

Gary - Stand with Ukraine

7,081
1
30
39

My alternative is to declare a boolean to track the initialized status of the record, which would be more error-prone but maybe more readable than exceptions (eventually I'm going to have a long list of if...elsif... statements testing a number of different variables.) Thanks! – Emery Lapinski Jun 11 '15 at 17:58

score 0 · Answer 3 · answered Oct 18 '20 at 03:10

0

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/

answered Oct 18 '20 at 03:10

Адександр Мороз

29
4

score 0 · Answer 4 · answered Aug 11 '22 at 12:40

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
$$;

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

4 Answers4