ERROR: failed to find conversion function from key_vals_nn to record[]

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

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');