Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"

Oh,

That's very interesting, thanks a lot for quick response. And have a nice evening.

With best regards,
Aleksandr Vinokourov

> On 12 Sep 2023, at 19:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes:
>> select *, item as item
>> from (select '[1]'::jsonb as items) as d
>> left join jsonb_array_elements(d.items) as item on true;
>
>> items | value | item
>> -------+-------+------
>> [1]   | 1     | 1
>
>> 1. It shows the name “value” for the column for which the elements were requested to be named “as item”,
>
> I see no bug here.  In the FROM entry "jsonb_array_elements(d.items) as
> item", you've declared the *table* alias to be "item", but you left
> the column name(s) of the table unspecified --- and jsonb_array_elements
> declares its output argument to be named "value":
>
> =# \sf jsonb_array_elements
> CREATE OR REPLACE FUNCTION pg_catalog.jsonb_array_elements(from_json jsonb, OUT value jsonb)
> RETURNS SETOF jsonb
> LANGUAGE internal
> IMMUTABLE PARALLEL SAFE STRICT ROWS 100
> AS $function$jsonb_array_elements$function$
>
> So "select *" expands the available columns as "items" from table "d"
> and "value" from table "item".  Referencing "item" in the SELECT list
> is really a whole-table reference, although this isn't too obvious
> because we hack that to act identical to a column reference if the
> reference is to a scalar-producing function.
>
> To clarify what's happening, you could specify the column alias
> explicitly:
>
> =# select *, item as item
> from (select '[1]'::jsonb as items) as d
> left join jsonb_array_elements(d.items) as item(zed) on true;
> items | zed | item
> -------+-----+------
> [1]   | 1   | 1
> (1 row)
>
> or even
>
> =# select *, item.zed as item
> from (select '[1]'::jsonb as items) as d
> left join jsonb_array_elements(d.items) as item(zed) on true;
> items | zed | item
> -------+-----+------
> [1]   | 1   | 1
> (1 row)
>
> Most scalar-producing functions don't declare an output argument
> name, and in that case "as foo" works effectively like "as foo(foo)"
> to set both the table and column alias.  I'm not sure why
> jsonb_array_elements goes out of its way to do this differently.
>
>            regards, tom lane