Hi
pá 19. 5. 2023 v 8:59 odesílatel Raymond Brinzer <ray.brinzer@xxxxxxxxx> 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.brinzer@xxxxxxxxx> 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.brinzer@xxxxxxxxx> 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
