Recent 11.16 release change

[Daniel Brinzila]

I am a bit confused as to the following change:


   -

   Stop using query-provided column aliases for the columns of whole-row
   variables that refer to plain tables (Tom Lane)

   The column names in tuples produced by a whole-row variable (such as
   tbl.* in contexts other than the top level of a SELECT list) are now
   always those of the associated named composite type, if there is one. We'd
   previously attempted to make them track any column aliases that had been
   applied to the FROM entry the variable refers to. But that's
   semantically dubious, because really then the output of the variable is not
   at all of the composite type it claims to be. Previous attempts to deal
   with that inconsistency had bad results up to and including storing
   unreadable data on disk, so just give up on the whole idea.

   In cases where it's important to be able to relabel such columns, a
   workaround is to introduce an extra level of sub-SELECT, so that the
   whole-row variable is referring to the sub-SELECT's output and not to a
   plain table. Then the variable is of type record to begin with and
   there's no issue.


Could someone please give an example of this scenario, one that works in
11.15 and another for 11.16 after the recent change.

Thanks!

Re: Recent 11.16 release change

[Daniel Brinzila]

Tom,

Thanks very much for the clarification, i was just going over the
regression tests and now makes sense.

However, is this the only function (row_to_json) or are there more of them
affected by this change?

Regards,
Daniel



On Tue, Jun 14, 2022 at 5:23 PM Tom Lane <
tgl_at_sss_pgh_pa_us_5s592v294c5771_de0d8...@icloud.com> wrote:

> Daniel Brinzila  writes:
> > I am a bit confused as to the following change:
> >Stop using query-provided column aliases for the columns of whole-row
> >variables that refer to plain tables (Tom Lane)
>
> > Could someone please give an example of this scenario, one that works in
> > 11.15 and another for 11.16 after the recent change.
>
> Here's the regression test example that changed behavior in that commit:
>
> regression=# select row_to_json(i) from int8_tbl i(x,y);
>   row_to_json
> 
>  {"q1":123,"q2":456}
>  {"q1":123,"q2":4567890123456789}
>  {"q1":4567890123456789,"q2":123}
>  {"q1":4567890123456789,"q2":4567890123456789}
>  {"q1":4567890123456789,"q2":-4567890123456789}
> (5 rows)
>
> The fields of the JSON output used to be labeled "x" and "y", after
> the column aliases of the FROM item.  But now that doesn't work and
> you get the table's original column names (which happen to be "q1"
> and "q2" in this test case).
>
> The workaround proposed in the release note is to do this if you
> need to relabel the columns of the whole-row variable "i":
>
> regression=# select row_to_json(i) from (select * from int8_tbl) i(x,y);
>  row_to_json
> --
>  {"x":123,"y":456}
>  {"x":123,"y":4567890123456789}
>  {"x":4567890123456789,"y":123}
>  {"x":4567890123456789,"y":4567890123456789}
>  {"x":4567890123456789,"y":-4567890123456789}
> (5 rows)
>
> With the extra sub-select, "i" is no longer of the named composite
> type associated with int8_tbl, but of an anonymous record type,
> so it can have the column names you want.
>
> regards, tom lane
>
>
>