Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1l4nyH-0007ys-Hz
	for pgsql-docs@arkaria.postgresql.org; Wed, 27 Jan 2021 16:47:09 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1l4nyG-0002FB-3e
	for pgsql-docs@arkaria.postgresql.org; Wed, 27 Jan 2021 16:47:08 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1l4nyF-0002F3-UK
	for pgsql-docs@lists.postgresql.org; Wed, 27 Jan 2021 16:47:07 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1l4nyD-0001Ut-KG
	for pgsql-docs@lists.postgresql.org; Wed, 27 Jan 2021 16:47:06 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 10RGl3Fb2402585;
	Wed, 27 Jan 2021 11:47:03 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: - <m7onov@gmail.com>
cc: pgsql-docs@lists.postgresql.org
Subject: Re: Better explanation of unnest with ordinality
In-reply-to: <FF1FB31F-0507-4F18-9559-2DE6E07E3B43@gmail.com>
References: <FF1FB31F-0507-4F18-9559-2DE6E07E3B43@gmail.com>
Comments: In-reply-to - <m7onov@gmail.com>
	message dated "Wed, 27 Jan 2021 18:44:11 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2402583.1611766023.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 27 Jan 2021 11:47:03 -0500
Message-ID: <2402584.1611766023@sss.pgh.pa.us>
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Precedence: bulk

- <m7onov@gmail.com> writes:
> 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 t=
he array index of the array element in the same tuple. The only relevant t=
hing about ordinality column I=E2=80=99ve found is here https://www.postgr=
esql.org/docs/13/queries-table-expressions.html <https://www.postgresql.or=
g/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 dr=
aft (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 collect=
ion. It states that in case of array argument and "with ordinality" this s=
tatement returns ordinality column that matches array indexes. Is it possi=
ble 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 in=
dex in array>?

Hmm.  I think 7.2.1.4 is clear enough:

    If the WITH ORDINALITY clause is specified, an additional column of
    type bigint will be added to the function result columns. This column
    numbers the rows of the function result set, starting from 1.

(The SELECT reference page is vaguer, though, which ought to be improved.)

What is less clear is the definition of UNNEST, for which that text refers
you to 9.19, which says only:

    Expands an array to a set of rows.

    unnest(ARRAY[1,2]) =E2=86=92
     1
     2

I'm inclined to expand that, now that we have room for
more-than-three-words-of-explanation, to

    Expands an array to a set of rows. Multi-dimensional arrays are read
    out in storage order.

    unnest(ARRAY[1,2]) =E2=86=92
     1
     2
    unnest(ARRAY[['foo','bar'],['baz','quux']]) =E2=86=92
     foo
     bar
     baz
     quux

I haven't checked your claim that the spec says that ordinality matches
the array indexes.  But it seems pretty meaningless for Postgres; what
would you do for multidimensional arrays?  Another thing that we have
that's not in the SQL spec is arrays with first index different from 1.
WITH ORDINALITY still counts from 1 in that case:

=3D# select * from unnest('[-1:2]=3D{1,2,3,4}'::int[]) with ordinality t;
 t | ordinality =

---+------------
 1 |          1
 2 |          2
 3 |          3
 4 |          4
(4 rows)

Maybe that's a spec violation, or maybe it isn't, but we're not
going to change it.  WITH ORDINALITY is implemented independently
of the particular SRF being expanded, so it couldn't take account
of the array subscripts even if we wanted it to.

			regards, tom lane