Date: Wed, 18 Mar 2020 20:37:58 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Erik Tews <erik@datenzone.de>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Better documentation for row_number() combined with set
 returning functions
Message-ID: <20200319003758.GD28341@momjian.us>
References: <e5116f96a53b73c1f3dff05d35218d146e622262.camel@datenzone.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <e5116f96a53b73c1f3dff05d35218d146e622262.camel@datenzone.de>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On Tue, Feb 25, 2020 at 08:24:13PM +0100, Erik Tews wrote:
> Hi
> 
> The current documentation of Postgresql sounds like row_number() over
> () can be used to number the rows returned by postgres. However, that

Yes, row_number() is a member of the set of window functions.

> doesn't work when the query also uses set returning functions such as
> json_array_elements. In this case, row_number() will be the same for
> every element of the set.

Yes, the issue is that a set-returning function in the target list
generates multiple rows while the other target list values are
duplicated, e.g.:

	CREATE TABLE test (x int);
	
	INSERT INTO test VALUES (1), (2), (3);
	
	SELECT x, generate_series(4,6) AS y, row_number() OVER () AS z FROM
	test;
	 x | y | z
	---+---+---
	 1 | 4 | 1
	 1 | 5 | 1
	 1 | 6 | 1
	 2 | 4 | 2
	 2 | 5 | 2
	 2 | 6 | 2
	 3 | 4 | 3
	 3 | 5 | 3
	 3 | 6 | 3

Notice the x=1 value is duplicated for the y values of 4,5,6, and the
row_number is duplicated too.

One way to avoid that is to do the expansion of the set-returning
function in a WITH query, and then apply row_number():

	WITH z AS (select x, generate_series(4,6) as y from test)
	select x, y, row_number() OVER () FROM z;
	 x | y | row_number
	---+---+------------
	 1 | 4 |          1
	 1 | 5 |          2
	 1 | 6 |          3
	 2 | 4 |          4
	 2 | 5 |          5
	 2 | 6 |          6
	 3 | 4 |          7
	 3 | 5 |          8
	 3 | 6 |          9

> I suggest to add a paragraph to the description of row_number() that
> states that this is the behavior and maybe also reference the "with
> ordinality" feature that can be used instead.

I can't see how ordinality could be used:

	SELECT x, z, ord FROM test, generate_series(4,6) WITH ORDINALITY AS a(z, ord);
	 x | z | ord
	---+---+-----
	 1 | 4 |   1
	 2 | 4 |   1
	 3 | 4 |   1
	 1 | 5 |   2
	 2 | 5 |   2
	 3 | 5 |   2
	 1 | 6 |   3
	 2 | 6 |   3
	 3 | 6 |   3

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +