From: - <m7onov@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_909F3CFB-F367-4C91-9ECF-FFEC3B4CB81F"
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.40.0.2.32\))
Subject: Better explanation of unnest with ordinality
Message-Id: <FF1FB31F-0507-4F18-9559-2DE6E07E3B43@gmail.com>
Date: Wed, 27 Jan 2021 18:44:11 +0300
To: pgsql-docs@lists.postgresql.org
Precedence: bulk


--Apple-Mail=_909F3CFB-F367-4C91-9ECF-FFEC3B4CB81F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hi guys!
I=E2=80=99ve recently come across a query of the kind:
select * from unnest(array[1,2,3,4]) with ordinality t;
and was asked whether ordinality value is guaranteed to be the same as =
the array index of the array element in the same tuple. The only =
relevant thing about ordinality column I=E2=80=99ve found is here =
https://www.postgresql.org/docs/13/queries-table-expressions.html =
<https://www.postgresql.org/docs/13/queries-table-expressions.html> but =
it=E2=80=99s not clear what is the order of function result set. =
According to ISO SQL:2011 standard draft (the only one I=E2=80=99ve =
found in internet) (7.6 <table reference>) unnest is not a function but =
a special syntax to make a table from collection. It states that in case =
of array argument and "with ordinality" this statement returns =
ordinality column that matches array indexes. Is it possible to clear it =
out in docs or maybe it=E2=80=99s an intentional deviation from standard =
so we can=E2=80=99t rely on <ordinality> =3D=3D <element index in =
array>?

--Apple-Mail=_909F3CFB-F367-4C91-9ECF-FFEC3B4CB81F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;" class=3D""><font =
face=3D"JetBrainsMono-Regular" class=3D"">Hi guys!<br class=3D"">I=E2=80=99=
ve recently come across a query of the kind:<br class=3D"">select * from =
unnest(array[1,2,3,4]) with ordinality t;</font><div class=3D""><font =
face=3D"JetBrainsMono-Regular" class=3D"">and was asked whether =
ordinality value is guaranteed to be the same as the array index of the =
array element in the same tuple. The only relevant thing about =
ordinality column I=E2=80=99ve found is here <a =
href=3D"https://www.postgresql.org/docs/13/queries-table-expressions.html"=
 =
class=3D"">https://www.postgresql.org/docs/13/queries-table-expressions.ht=
ml</a>&nbsp;but it=E2=80=99s not clear what is the order of function =
result set. According to ISO SQL:2011 standard draft (the only one =
I=E2=80=99ve found in internet) (7.6 &lt;table reference&gt;) unnest is =
not a function but a special syntax to make a&nbsp;table from =
collection. It states that in case of array argument and "with =
ordinality" this statement returns ordinality column that matches array =
indexes. Is it possible to clear it out in docs or maybe it=E2=80=99s an =
intentional deviation from standard so we can=E2=80=99t rely on =
&lt;ordinality&gt; =3D=3D &lt;element index in array&gt;?</font><br =
class=3D""></div></body></html>=

--Apple-Mail=_909F3CFB-F367-4C91-9ECF-FFEC3B4CB81F--