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
Date: Wed, 18 Mar 2020 20:37:58 -0400
Message-ID: <20200319003758.GD28341@momjian.us> (raw)
In-Reply-To: <e5116f96a53b73c1f3dff05d35218d146e622262.camel@datenzone.de>
References: <e5116f96a53b73c1f3dff05d35218d146e622262.camel@datenzone.de>
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 +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, erik@datenzone.de, pgsql-docs@lists.postgresql.org
Subject: Re: Better documentation for row_number() combined with set returning functions
In-Reply-To: <20200319003758.GD28341@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox