Records, Types, and Arrays

Greetings, all.

It's been a down-the-rabbit-hole day for me. It all started out with a
simple problem. I have defined a composite type. There are functions
which return arrays whose values would be suitable to the type I defined.
How do I turn arrays into composite typed values?

Conceptually, this is straightforward. Any given array can be mapped to a
corresponding record with the same elements, so this expression would make
sense:

ARRAY[1,2,3]::RECORD

If the result happens to be a valid instance of my_type, you might say:

ARRAY[1,2,3]::RECORD::my_type

Or, ideally, just:

ARRAY[1,2,3]::my_type

It seems to be a rather long way from the idea to the implementation,
however. A helpful soul from the IRC channel did manage to make this
happen in a single expression:

(format('(%s)', array_to_string(the_array, ','))::my_type).*

While I'm happy to have it, that's ugly even by SQL's syntactic yardstick.
So, I figured I'd see about hiding it behind a function and a custom cast.
These efforts have not been successful, for reasons I'll probably share in
a subsequent email, as the details would distract from the point of this
one.

Getting to that point... we have these three kinds of things:

* Arrays
* Composite Values / Records
* Typed Composite Values (instances of composite types)

(Note on the second: while section 8.16.2 of the documentation talks about
constructing "composite values", pg_typeof() reports these to be of the
"record" pseudo-type. To (hopefully) avoid confusion, I'm going to
exclusively say "record" here.)

Here's the thing about these: in the abstract, they're mostly the same. A
record is simply an ordered multiset. If you ignore implementation,
syntax, and whatnot, you could say that arrays are the subset of records
where all the members are of the same type. Objects of composite type can
be considered records with an additional feature: each member has a name.

It seems to me, then, that:

1) Switching between these things should be dead easy; and
2) One should be able to treat them as similarly as their actual
differences allow.

On the first point (speaking of arrays and composite types generically),
there are six possible casts. One of these already works, when members are
compatible:

record::composite_type

(Mostly, anyway; I did run into a kink with it, which I'll explain when I
discuss what I've tried.)

These casts would always be valid:

array::record
composite_type::record

These would be valid where the member sets are compatible:

array::composite_type
record::array
composite_type::array

It seems like having all six casts available would be very handy. But
(here's point 2) to the extent that you don't have to bother switching
between them at all, so much the better. For instance:

(ARRAY[5,6,7])[1]
(ROW(5,6,7))[1]
(ROW(5,6,7)::my_type)[1]

all make perfect sense. It would be lovely to be able to treat these types
interchangeably where appropriate. It seems to me (having failed to
imagine a counterexample) that any operation you could apply to an array
should be applicable to a record, and any operation you could apply to a
record should be applicable to an instance of a composite type.

While the second point is rather far-reaching and idealistic, the first
seems well-defined and reasonably easy.

If you've taken the time to read all this, thank you. If you take the idea
seriously, or have practical suggestions, thank you even more. If you
correct me on something important... well, I owe much of what I know to
people like you, so please accept my deepest gratitude.

--
Yours,

Ray Brinzer