I’ve copied a self-contained testcase below. Is the error that the "as intended" test causes due to a known limitation—or even a semantic dilemma that I'm failing to spot? Or might it be due to a bug?
If you're interested, the testcase rests on this thinking:
Many SQL experts claim that the RDBMS pioneers made a mistake when they made data types nullable by default—and they argue that the optional modifier should have been *nullable*. Anyway, the philosophy is clear:
«
Nulls bring all sorts of risks to the programmer that are trivially avoided in most cases where you don’t anyway want nulls.
»
However, it’s a pain to have to remember to write "not null" in a gazillion places. And the user-defined domain (conspicuous by its absence in Oracle Database) is the perfect device to impose your intentions from a single point of maintenance.
I've gone to the max. with the "nn" domain approach in my testcase. It uses a composite type thus:
type key_val as (k text_nn, v text_nn);
(At least the "text_nn" idea doesn't cause problems.)
It uses the "any()" array function to test if a given "key_val" value is found in an array of such values.
The error that occurs when I write what I mean, using a "key_val_nn" value and a "key_vals_nn" value.
I can work around the problem by typecasting my values back to their base "key_val" and "key_val[]" values by hand.
So I'm surprised that PG can't manage this typecasting for itself.
——————————————————————————————
create domain text_nn as text not null;
create type key_val as (k text_nn, v text_nn);
create domain key_val_nn as key_val not null;
create domain key_vals_nn as key_val_nn[] not null;
create function f(which in text)
returns text
language plpgsql
as $body$
declare
-- Use the raw composite type.
kv1 constant key_val := ('a', 'b');
kv2 constant key_val := ('a', 'b');
kvs constant key_val[] := array[kv1, kv2];
-- Use the domains that bring null-safety.
kv1_nn constant key_val_nn := ('a', 'b');
kvs_nn constant key_vals_nn := array[kv1, kv2];
-- Typecast the null-safe values back to the raw composite type.
kv1_0 constant key_val := kv1_nn;
kvs_0 constant key_val[] := kvs_nn;
begin
case which
when 'without NOT NULL safety' then
return (kv1 = any(kvs));
when 'as intended' then
return (kv1_nn = any(kvs_nn));
when 'workaround' then
return (kv1_0 = any(kvs_0));
end case;
end;
$body$;
select f('without NOT NULL safety');
select f('workaround');
/*
This one cases the error, thus:
ERROR: failed to find conversion function from key_vals_nn to record[]
CONTEXT: SQL expression "(kv1_nn = any(kvs_nn))"
*/;
select f('as intended');