PostgreSQL Parametric Window Size

Question

Does anyone know if SQL Standards define and PostgreSQL will implement (10.x or higher) Parametric Window Size?

Bellow a MVE of what I call a parametric window size:

WITH

D AS (SELECT T.x::FLOAT FROM generate_series(0., 10., 0.1) AS T(x)),
W AS (SELECT 10 AS WindowSize)

SELECT
    D.x
   ,AVG(D.x) OVER (ROWS BETWEEN W.WindowSize PRECEDING AND CURRENT ROW)
FROM
    D, W;

Which raises the following error:

ERROR:  argument of ROWS must not contain variables
LINE 8:    ,AVG(D.x) OVER (ROWS BETWEEN W.WindowSize PRECEDING AND C...
                                        ^
********** Error **********

ERROR: argument of ROWS must not contain variables
SQL State: 42P10

I understand parametric window is kind of complex to implement. And I know that I can bypass some aspects of this limitation using Dynamic Query and PL/PGSQL.

My questions are:

Do SQL Standards define such a thing?
Will PostgreSQL implement it (if so, when is it planned)?
If not, what are limitations to overcome before having such a feature?

It can't be that hard to implement -- at least for ROWS. It is pretty easy to get the functionality in other ways, but that is not your question. — Gordon Linoff
– Gordon Linoff, Commented Oct 10, 2017 at 11:28

Markus Winand · Accepted Answer · 2017-10-10 11:28:42Z

2

No, this is not standard SQL.

You can check this in the BNF:

http://jakewheat.github.io/sql-overview/sql-2008-foundation-grammar.html#window-frame-preceding

<window frame preceding> ::=
  <unsigned value specification> PRECEDING

<unsigned value specification> does not allow expressions. Bind parameters are allowed, btw.

answered Oct 10, 2017 at 11:28

Markus Winand

8,8541 gold badge38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jlandercy Over a year ago

Thanks for sharing the link, is it an official reference? Does it cover the complete SQL definition?

Markus Winand Over a year ago

No, this is not official. But I've checked the 2016 relase – no obvious difference. The linked BNF claims to cover part 2 of the ISO standard. See here what the different parts are: modern-sql.com/standard/parts

Collectives™ on Stack Overflow

PostgreSQL Parametric Window Size

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related