Composite IS NULL again, this time with plpgsql

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Composite IS NULL again, this time with plpgsql
Date: 2017-03-18 22:40:42
Message-ID: 8760j6b6ef.fsf@news-spur.riddles.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-03-18 22:57:38 Re: Composite IS NULL again, this time with plpgsql
Previous Message Peter Geoghegan 2017-03-18 22:22:43 Re: [PATCH] SortSupport for macaddr type