Records, Types, and Arrays

On Thu, May 18, 2023 at 10:06 PM Raymond Brinzer <ray.brinzer@gmail.com>
wrote:

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.

On Fri, May 19, 2023 at 1:42 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, May 18, 2023 at 10:06 PM Raymond Brinzer <ray.brinzer@gmail.com>
wrote:

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

Assuming one wants to write a specific solution, rather than a general one,
sure. And when you want to deal with an unnamed array returned from a
function? Well, you can throw *that* in a CTE to give it a name, or
perform some other such contortion. The aggregate load of having to phrase
such simple ideas in complicated ways really isn't good.

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.

The problem with "easier" is that addressing directly in front of you is
always easier in the immediate sense than actually attacking the problem
itself. It also dooms you to the (after)life of Sisyphus, always rolling
the same rock up the same hill.

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.

Well, making one small part of it work would be a boon to me. Is a simple,
generic cast from an array to a record really rocket science? I can't
imagine why that would be.

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.

Naturally, you wouldn't give up the ability to do that. You'd merely gain
the ability to do it another way.

--
Ray Brinzer

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

On Thursday, May 18, 2023, Raymond Brinzer <ray.brinzer@gmail.com> wrote:

scratch=# select row(2,3)::test_type;

Unknown typed value, immediately converted to a known concrete instance of
test_type. It is never actually resolved as record.

All of the others must concretely be resolved to record to escape their
query level, and if you then try to cast the concrete record to some other
concrete type a cast needs to exist.

David J.

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?

On Fri, May 19, 2023 at 2:48 AM Raymond Brinzer <ray.brinzer@gmail.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.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

--
Ray Brinzer

Hi

pá 19. 5. 2023 v 8:59 odesílatel Raymond Brinzer <ray.brinzer@gmail.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:58 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thursday, May 18, 2023, Raymond Brinzer <ray.brinzer@gmail.com> wrote:

scratch=# select row(2,3)::test_type;

Unknown typed value, immediately converted to a known concrete instance of
test_type. It is never actually resolved as record.

All of the others must concretely be resolved to record to escape their
query level, and if you then try to cast the concrete record to some other
concrete type a cast needs to exist.

I see. That suggests, oddly, that pg_typeof() is changing the thing it's
observing, because row(2,3) was not a record but an 'unknown typed value'
before pg_typeof() was called on it. Good to know.

CREATE CAST seems not to like working with pseudo-types. Neither the
source nor the target can be 'anyarray' or 'record'. So that seems out.

On the other hand, the pseudo-type doc says, 'Functions coded in C (whether
built-in or dynamically loaded) can be declared to accept or return any of
these pseudo data types.' I'm assuming that an 'unknown typed value"
corresponds to the 'unknown' pseudo-type. So it seems like a C function
which took an anyarray as its parameter, and returned a value of type
'unknown' would bypass the need for a specifically defined cast. (Emphasis
on "seems".)
--
Ray Brinzer

The convention here is to inline or, at worse, bottom post, trimming
irrelevant context.

On Friday, May 19, 2023, Raymond Brinzer <ray.brinzer@gmail.com> wrote:

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?

Row() is pure syntax. It distinguishes (col) vs. row(col) where the first
is just a column in parentheses and the second is a composite with one
column member.

David J.