Re: Records, Types, and Arrays

On a problem which came up while trying to implement a solution, perhaps
someone could explain this:

scratch=# create type test_type as (a int, b int);
CREATE TYPE
scratch=# create function get_row() returns record as $$ select row(2,3);
$$ language sql;
CREATE FUNCTION
scratch=# select get_row();
get_row
---------
(2,3)
(1 row)

scratch=# select pg_typeof( get_row() );
pg_typeof
-----------
record
(1 row)

scratch=# select pg_typeof( row(2,3) );
pg_typeof
-----------
record
(1 row)

scratch=# select row(2,3)::test_type;
row
-------
(2,3)
(1 row)

scratch=# select get_row()::test_type;
ERROR: cannot cast type record to test_type
LINE 1: select get_row()::test_type;

If row(2,3) and get_row() are both of type record, and the records have the
same values, why can one be cast to test_type, and the other not?

On Fri, May 19, 2023 at 1:07 AM Raymond Brinzer <ray(dot)brinzer(at)gmail(dot)com>
wrote:

> 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
>

--
Ray Brinzer