Nested window functions not permitted

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Nested window functions not permitted
Date: 2015-11-08 22:37:39
Message-ID: 1447022259.4444.28.camel@phoenix
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I tried to do this:

SELECT p.company, p.start, p.yearend, p.idnum,
s.pdno, s.pdend,
CASE WHEN nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) OVER w IS NULL
THEN p.start
ELSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) + '1 day'::INTERVAL
END AS spdstart
FROM period AS p,
subperiod AS s
WHERE p.company = s.company AND p.yearend = s.yearend
WINDOW w AS (PARTITION BY p.start ORDER BY s.pdno)
ORDER BY p.yearend, s.pdno;

in order to get these results:

company | start | yearend | idnum | pdno | pdend | spdstart
---------+------------+------------+-------+------+------------+-----------
GBS | 2014-02-01 | 2015-01-31 | 1 | 1 | 2014-04-30 | 2014-02-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 2 | 2014-07-31 | 2014-05-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 3 | 2014-10-31 | 2014-08-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 4 | 2015-01-31 | 2014-11-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 1 | 2015-04-30 | 2015-02-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 2 | 2015-07-31 | 2015-05-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 3 | 2015-10-31 | 2015-08-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 4 | 2016-01-31 | 2015-11-01
(8 rows)

but apparently nesting window functions is not permitted (in 9.3.10).
Is this possible in later versions of PG? or is there any other way to
look at a value from the previous row?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-11-08 22:50:12 Re: Nested window functions not permitted
Previous Message Adrian Klaver 2015-11-08 14:45:02 Re: Error when test on DBT2 Postgresql