This came up recently on irc:
create type t1 as (a integer, b integer);
create type t2 as (p t1, q t1);
create function null_t2() returns t2 language sql as $f$ select null::t2; $f$;
Now consider the following plpgsql:
declare v t2;
begin v := null_t2(); raise info 'v is null = %', v is null; -- shows 'f'
end;
The value of the plpgsql variable v always behaves as if it had been
assigned row(row(null,null),row(null,null)) -- which, as we thrashed out
in detail sometime last year, isn't the same as row(null,null) or just
null and isn't considered null by IS NULL.
So there's no non-ugly way to preserve the nullity or otherwise of the
function result. The best I could come up with in answer to the original
problem (how to detect in plpgsql whether a composite value returned by
a function was actually null) was this:
declare v t2; v_null boolean;
begin select x into v from null_t2() as x where not (x is null); v_null := not found; raise info 'v is null = %',
v_null;
end;
which lacks a certain obviousness (and you have to remember to use not
(x is null) rather than x is not null).
This obviously happens because plpgsql is storing the variable as an
expanded list of fields rather than as a single composite datum, and
rebuilding the datum value when it needs to be passed to SQL for
evaluation. Without an "isnull" flag for each composite subvalue, this
can't regenerate the original datum closely enough to give the same
value on an isnull test.
What to do about this?
--
Andrew (irc:RhodiumToad)