Re: Records, Types, and Arrays - Mailing list pgsql-general

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.brinzer@gmail.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