pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Better documentation for row_number() combined with set returning functions
2+ messages / 2 participants
[nested] [flat]

* Better documentation for row_number() combined with set returning functions
@ 2020-02-25 19:24 Erik Tews <erik@datenzone.de>
  2020-03-19 00:37 ` Re: Better documentation for row_number() combined with set returning functions Bruce Momjian <bruce@momjian.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Erik Tews @ 2020-02-25 19:24 UTC (permalink / raw)
  To: pgsql-docs@lists.postgresql.org

Hi

The current documentation of Postgresql sounds like row_number() over
() can be used to number the rows returned by postgres. However, that
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.

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

Erik

Attachments:

  signature.asc (application/pgp-signature, 487B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Better documentation for row_number() combined with set returning functions
  2020-02-25 19:24 Better documentation for row_number() combined with set returning functions Erik Tews <erik@datenzone.de>
@ 2020-03-19 00:37 ` Bruce Momjian <bruce@momjian.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Bruce Momjian @ 2020-03-19 00:37 UTC (permalink / raw)
  To: Erik Tews <erik@datenzone.de>; +Cc: pgsql-docs@lists.postgresql.org

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 +





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2020-03-19 00:37 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-25 19:24 Better documentation for row_number() combined with set returning functions Erik Tews <erik@datenzone.de>
2020-03-19 00:37 ` Bruce Momjian <bruce@momjian.us>

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