From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Erik Tews <erik(at)datenzone(dot)de> |
Cc: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Better documentation for row_number() combined with set returning functions |
Date: | 2020-03-19 00:37:58 |
Message-ID: | 20200319003758.GD28341@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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(at)momjian(dot)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 +
From | Date | Subject | |
---|---|---|---|
Next Message | Corey Huinker | 2020-03-19 02:34:25 | Re: Add A Glossary |
Previous Message | Daniel Gustafsson | 2020-03-18 21:56:02 | Re: Incorrect dropuser command in postgress 11 |