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