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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
