Re: Records, Types, and Arrays

Hi

pá 19. 5. 2023 v 8:59 odesílatel Raymond Brinzer <ray(dot)brinzer(at)gmail(dot)com>
napsal:

> Sorry, I should have noted this as well:
>
> "One should also realize that when a PL/pgSQL function is declared to
> return type record, this is not quite the same concept as a record
> variable, even though such a function might use a record variable to hold
> its result. In both cases the actual row structure is unknown when the
> function is written, but for a function returning record the actual
> structure is determined when the calling query is parsed, whereas a record
> variable can change its row structure on-the-fly."
>
> I'm guessing that row() isn't really a function, then? And even so,
> assuming this is the important difference, how is the ability to change row
> structure on the fly making the cast possible? In what way would the query
> calling get_row() be critical?
>

plpgsql cannot work well with too dynamic data. If you need more dynamic
data, then using jsonb is probably the best idea now.

Regards

Pavel

> On Fri, May 19, 2023 at 2:48 AM Raymond Brinzer <ray(dot)brinzer(at)gmail(dot)com>
> wrote:
>
>> 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
>>
>
>
> --
> Ray Brinzer
>