How do I turn arrays into composite typed values?
Using just SQL syntax and no string munging:
(array_val[1]::col1_type, array_val[2]::col2_type)::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.
None of what you are saying is likely to ever see the light of day. If you want to learn the SQL-way might be easier to just forget about your idealized equivalency between composite types and array containers.
ARRAY[...] is a constructor, its output is an array container. You can either type the elements within the constructor or leave them untyped and put a syntactical-sugar cast on the result.
ARRAY['2023-01-01'::date,'2023-02-01'::date]
ARRAY['2023-01-01','2023-02-01']::date[]
While you've said that having all this stuff would "be quite handy" that isn't obvious to me. It is even less obvious that any benefit would likely be small compared to the effort to make all of this actually work.
Even if I could write: composite_type[1] instead of composite_type.column1 I don't know why I'd want to give up the expressiveness of writing the column name.
David J.