From: David Fetter <david@fetter.org>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Early WIP/PoC for inlining CTEs
Date: Wed, 25 Jul 2018 07:42:37 +0200
Message-ID: <20180725054236.GB17411@fetter.org> (raw)
In-Reply-To: <87sh48ffhb.fsf@news-spur.riddles.org.uk>
References: <87sh48ffhb.fsf@news-spur.riddles.org.uk>
On Tue, Jul 24, 2018 at 11:28:21PM +0100, Andrew Gierth wrote:
> About a year ago I was briefly in discussion/collaboration with Adam Sah
> regarding the topic of inlining CTEs into the query rather than treating
> them as optimization barriers. We didn't take it very far (he sent me
> some stuff, I wrote some stuff and sent it back, things kind of got
> dropped at that point); but there's been some recent discussion of this
> and some people have expressed an interest in seeing the code.
>
> So I'm posting the parts that I wrote for the benefit of anyone wanting
> to pick up the issue again. The assumption of this code is that some
> form of syntax would exist to mark materialized CTEs and set the
> "ctematerialized" flag.
>
> I haven't rebased this or tested it since last year; this patch is
> against b81eba6a65.
Please find attached a version rebased atop 167075be3ab1547e18 with
what I believe are appropriate changes to regression test output. The
other changes to the regression tests output are somewhat puzzling, as
they change the actual results of queries. I've also attached both
the "leftover" diff and the files to which it should be applied.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
*** /home/shackle/pggit/postgresql/src/test/regress/expected/window.out 2018-07-24 22:14:09.015838790 -0700
--- /home/shackle/pggit/postgresql/src/test/regress/results/window.out 2018-07-24 22:20:36.766079578 -0700
***************
*** 3297,3303 ****
);
eq1 | eq2 | eq3
-----+-----+-----
! t | t | t
(1 row)
--
--- 3297,3303 ----
);
eq1 | eq2 | eq3
-----+-----+-----
! f | f | t
(1 row)
--
======================================================================
*** /home/shackle/pggit/postgresql/src/test/regress/expected/with.out 2018-07-24 22:14:09.017838781 -0700
--- /home/shackle/pggit/postgresql/src/test/regress/results/with.out 2018-07-24 22:20:37.562075966 -0700
***************
*** 18,24 ****
) ss;
count
-------
! 5
(1 row)
-- WITH RECURSIVE
--- 18,24 ----
) ss;
count
-------
! 10
(1 row)
-- WITH RECURSIVE
======================================================================
--
-- Tests for common table expressions (WITH query, ... SELECT ...)
--
-- Basic WITH
WITH q1(x,y) AS (SELECT 1,2)
SELECT * FROM q1, q1 AS q2;
x | y | x | y
---+---+---+---
1 | 2 | 1 | 2
(1 row)
-- Multiple uses are evaluated only once
SELECT count(*) FROM (
WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
SELECT * FROM q1
UNION
SELECT * FROM q1
) ss;
count
-------
5
(1 row)
-- WITH RECURSIVE
-- sum of 1..100
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
sum
------
5050
(1 row)
WITH RECURSIVE t(n) AS (
SELECT (VALUES(1))
UNION ALL
SELECT n+1 FROM t WHERE n < 5
)
SELECT * FROM t;
n
---
1
2
3
4
5
(5 rows)
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums WHERE n < 5;
SELECT * FROM nums;
n
---
1
2
3
4
5
(5 rows)
CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums WHERE n < 6;
SELECT * FROM nums;
n
---
1
2
3
4
5
6
(6 rows)
-- This is an infinite loop with UNION ALL, but not with UNION
WITH RECURSIVE t(n) AS (
SELECT 1
UNION
SELECT 10-n FROM t)
SELECT * FROM t;
n
---
1
9
(2 rows)
-- This'd be an infinite loop, but outside query reads only as much as needed
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t)
SELECT * FROM t LIMIT 10;
n
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
-- UNION case should have same property
WITH RECURSIVE t(n) AS (
SELECT 1
UNION
SELECT n+1 FROM t)
SELECT * FROM t LIMIT 10;
n
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
-- Test behavior with an unknown-type literal in the WITH
WITH q AS (SELECT 'foo' AS x)
SELECT x, x IS OF (text) AS is_text FROM q;
x | is_text
-----+---------
foo | t
(1 row)
WITH RECURSIVE t(n) AS (
SELECT 'foo'
UNION ALL
SELECT n || ' bar' FROM t WHERE length(n) < 20
)
SELECT n, n IS OF (text) AS is_text FROM t;
n | is_text
-------------------------+---------
foo | t
foo bar | t
foo bar bar | t
foo bar bar bar | t
foo bar bar bar bar | t
foo bar bar bar bar bar | t
(6 rows)
-- In a perfect world, this would work and resolve the literal as int ...
-- but for now, we have to be content with resolving to text too soon.
WITH RECURSIVE t(n) AS (
SELECT '7'
UNION ALL
SELECT n+1 FROM t WHERE n < 10
)
SELECT n, n IS OF (int) AS is_int FROM t;
ERROR: operator does not exist: text + integer
LINE 4: SELECT n+1 FROM t WHERE n < 10
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
--
-- Some examples with a tree
--
-- department structure represented here is as follows:
--
-- ROOT-+->A-+->B-+->C
-- | |
-- | +->D-+->F
-- +->E-+->G
CREATE TEMP TABLE department (
id INTEGER PRIMARY KEY, -- department ID
parent_department INTEGER REFERENCES department, -- upper department ID
name TEXT -- department name
);
INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 5, 'G');
-- extract all departments under 'A'. Result should be A, B, C, D and F
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT name as root_name, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
root_name | id | parent_department | name
-----------+----+-------------------+------
A | 1 | 0 | A
A | 2 | 1 | B
A | 3 | 2 | C
A | 4 | 2 | D
A | 6 | 4 | F
(5 rows)
-- extract all departments under 'A' with "level" number
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
-- non recursive term
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
level | id | parent_department | name
-------+----+-------------------+------
1 | 1 | 0 | A
2 | 2 | 1 | B
3 | 3 | 2 | C
3 | 4 | 2 | D
4 | 6 | 4 | F
(5 rows)
-- extract all departments under 'A' with "level" number.
-- Only shows level 2 or more
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
-- non recursive term
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
level | id | parent_department | name
-------+----+-------------------+------
2 | 2 | 1 | B
3 | 3 | 2 | C
3 | 4 | 2 | D
4 | 6 | 4 | F
(4 rows)
-- "RECURSIVE" is ignored if the query has no self-reference
WITH RECURSIVE subdepartment AS
(
-- note lack of recursive UNION structure
SELECT * FROM department WHERE name = 'A'
)
SELECT * FROM subdepartment ORDER BY name;
id | parent_department | name
----+-------------------+------
1 | 0 | A
(1 row)
-- inside subqueries
SELECT count(*) FROM (
WITH RECURSIVE t(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
)
SELECT * FROM t) AS t WHERE n < (
SELECT count(*) FROM (
WITH RECURSIVE t(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
)
SELECT * FROM t WHERE n < 50000
) AS t WHERE n < 100);
count
-------
98
(1 row)
-- use same CTE twice at different subquery levels
WITH q1(x,y) AS (
SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
)
SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
count
-------
50
(1 row)
-- via a VIEW
CREATE TEMPORARY VIEW vsubdepartment AS
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment;
SELECT * FROM vsubdepartment ORDER BY name;
id | parent_department | name
----+-------------------+------
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
6 | 4 | F
(5 rows)
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
pg_get_viewdef
-----------------------------------------------
WITH RECURSIVE subdepartment AS ( +
SELECT department.id, +
department.parent_department, +
department.name +
FROM department +
WHERE (department.name = 'A'::text)+
UNION ALL +
SELECT d.id, +
d.parent_department, +
d.name +
FROM department d, +
subdepartment sd +
WHERE (d.parent_department = sd.id)+
) +
SELECT subdepartment.id, +
subdepartment.parent_department, +
subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
pg_get_viewdef
---------------------------------------------
WITH RECURSIVE subdepartment AS ( +
SELECT department.id, +
department.parent_department, +
department.name +
FROM department +
WHERE department.name = 'A'::text+
UNION ALL +
SELECT d.id, +
d.parent_department, +
d.name +
FROM department d, +
subdepartment sd +
WHERE d.parent_department = sd.id+
) +
SELECT subdepartment.id, +
subdepartment.parent_department, +
subdepartment.name +
FROM subdepartment;
(1 row)
-- Another reverse-listing example
CREATE VIEW sums_1_100 AS
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
\d+ sums_1_100
View "public.sums_1_100"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
sum | bigint | | | | plain |
View definition:
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT t_1.n + 1
FROM t t_1
WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
-- corner case in which sub-WITH gets initialized first
with recursive q as (
select * from department
union all
(with x as (select * from q)
select * from x)
)
select * from q limit 24;
id | parent_department | name
----+-------------------+------
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
(24 rows)
with recursive q as (
select * from department
union all
(with recursive x as (
select * from department
union all
(select * from q union all select * from x)
)
select * from x)
)
select * from q limit 32;
id | parent_department | name
----+-------------------+------
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
(32 rows)
-- recursive term has sub-UNION
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j+1 FROM
(SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
JOIN t ON (t2.i = t.i+1))
SELECT * FROM t;
i | j
---+---
1 | 2
2 | 3
3 | 4
(3 rows)
--
-- different tree example
--
CREATE TEMPORARY TABLE tree(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES tree(id)
);
INSERT INTO tree
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
--
-- get all paths from "second level" nodes to leaf nodes
--
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
(t1.path[1] = t2.path[1] AND
array_upper(t1.path,1) = 1 AND
array_upper(t2.path,1) > 1)
ORDER BY t1.id, t2.id;
id | path | id | path
----+------+----+-------------
2 | {2} | 4 | {2,4}
2 | {2} | 5 | {2,5}
2 | {2} | 6 | {2,6}
2 | {2} | 9 | {2,4,9}
2 | {2} | 10 | {2,4,10}
2 | {2} | 14 | {2,4,9,14}
3 | {3} | 7 | {3,7}
3 | {3} | 8 | {3,8}
3 | {3} | 11 | {3,7,11}
3 | {3} | 12 | {3,7,12}
3 | {3} | 13 | {3,7,13}
3 | {3} | 15 | {3,7,11,15}
3 | {3} | 16 | {3,7,11,16}
(13 rows)
-- just count 'em
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
(t1.path[1] = t2.path[1] AND
array_upper(t1.path,1) = 1 AND
array_upper(t2.path,1) > 1)
GROUP BY t1.id
ORDER BY t1.id;
id | count
----+-------
2 | 6
3 | 7
(2 rows)
-- this variant tickled a whole-row-variable bug in 8.4devel
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
(t1.id=t2.id);
id | path | t2
----+-------------+--------------------
1 | {} | (1,{})
2 | {2} | (2,{2})
3 | {3} | (3,{3})
4 | {2,4} | (4,"{2,4}")
5 | {2,5} | (5,"{2,5}")
6 | {2,6} | (6,"{2,6}")
7 | {3,7} | (7,"{3,7}")
8 | {3,8} | (8,"{3,8}")
9 | {2,4,9} | (9,"{2,4,9}")
10 | {2,4,10} | (10,"{2,4,10}")
11 | {3,7,11} | (11,"{3,7,11}")
12 | {3,7,12} | (12,"{3,7,12}")
13 | {3,7,13} | (13,"{3,7,13}")
14 | {2,4,9,14} | (14,"{2,4,9,14}")
15 | {3,7,11,15} | (15,"{3,7,11,15}")
16 | {3,7,11,16} | (16,"{3,7,11,16}")
(16 rows)
--
-- test cycle detection
--
create temp table graph( f int, t int, label text );
insert into graph values
(1, 2, 'arc 1 -> 2'),
(1, 3, 'arc 1 -> 3'),
(2, 3, 'arc 2 -> 3'),
(1, 4, 'arc 1 -> 4'),
(4, 5, 'arc 4 -> 5'),
(5, 1, 'arc 5 -> 1');
with recursive search_graph(f, t, label, path, cycle) as (
select *, array[row(g.f, g.t)], false from graph g
union all
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
from graph g, search_graph sg
where g.f = sg.t and not cycle
)
select * from search_graph;
f | t | label | path | cycle
---+---+------------+-------------------------------------------+-------
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
(25 rows)
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, path, cycle) as (
select *, array[row(g.f, g.t)], false from graph g
union all
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
from graph g, search_graph sg
where g.f = sg.t and not cycle
)
select * from search_graph order by path;
f | t | label | path | cycle
---+---+------------+-------------------------------------------+-------
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
(25 rows)
--
-- test multiple WITH queries
--
WITH RECURSIVE
y (id) AS (VALUES (1)),
x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
SELECT * FROM x;
id
----
1
2
3
4
5
(5 rows)
-- forward reference OK
WITH RECURSIVE
x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS (values (1))
SELECT * FROM x;
id
----
1
2
3
4
5
(5 rows)
WITH RECURSIVE
x(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
id | id
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 |
7 |
8 |
9 |
10 |
(10 rows)
WITH RECURSIVE
x(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
id | id
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 |
(6 rows)
WITH RECURSIVE
x(id) AS
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
y(id) AS
(SELECT * FROM x UNION ALL SELECT * FROM x),
z(id) AS
(SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
SELECT * FROM z;
id
----
1
2
3
2
3
4
3
4
5
4
5
6
5
6
7
6
7
8
7
8
9
8
9
10
9
10
10
(27 rows)
WITH RECURSIVE
x(id) AS
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
y(id) AS
(SELECT * FROM x UNION ALL SELECT * FROM x),
z(id) AS
(SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
SELECT * FROM z;
id
----
1
2
3
1
2
3
2
3
4
2
3
4
3
4
5
3
4
5
4
5
6
4
5
6
5
6
7
5
6
7
6
7
8
6
7
8
7
8
9
7
8
9
8
9
10
8
9
10
9
10
9
10
10
10
(54 rows)
--
-- Test WITH attached to a data-modifying statement
--
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
WITH t AS (
SELECT a FROM y
)
INSERT INTO y
SELECT a+20 FROM t RETURNING *;
a
----
21
22
23
24
25
26
27
28
29
30
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
24
25
26
27
28
29
30
(20 rows)
WITH t AS (
SELECT a FROM y
)
UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)
WITH RECURSIVE t(a) AS (
SELECT 11
UNION ALL
SELECT a+1 FROM t WHERE a < 50
)
DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
DROP TABLE y;
--
-- error cases
--
-- INTERSECT
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
^
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
^
-- EXCEPT
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
^
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
^
-- no non-recursive term
WITH RECURSIVE x(n) AS (SELECT n FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
^
-- recursive term in the left hand side (strictly speaking, should allow this)
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within its non-recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
^
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
^
-- RIGHT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
^
-- FULL JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
^
-- subquery
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
WHERE n IN (SELECT * FROM x))
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within a subquery
LINE 2: WHERE n IN (SELECT * FROM x))
^
-- aggregate functions
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
SELECT * FROM x;
ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
^
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
SELECT * FROM x;
ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
^
-- ORDER BY
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
SELECT * FROM x;
ERROR: ORDER BY in a recursive query is not implemented
LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
^
-- LIMIT/OFFSET
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
SELECT * FROM x;
ERROR: OFFSET in a recursive query is not implemented
LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
^
-- FOR UPDATE
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
SELECT * FROM x;
ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
-- target list has a recursive query name
WITH RECURSIVE x(id) AS (values (1)
UNION ALL
SELECT (SELECT * FROM x) FROM x WHERE id < 5
) SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within a subquery
LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
^
-- mutual recursive query (not implemented)
WITH RECURSIVE
x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
SELECT * FROM x;
ERROR: mutual recursion between WITH items is not implemented
LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
^
-- non-linear recursion is not allowed
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
UNION ALL
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
SELECT * FROM
(SELECT i+1 FROM foo WHERE i < 10
UNION ALL
SELECT i+1 FROM foo WHERE i < 5) AS t
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
EXCEPT
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear within EXCEPT
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
INTERSECT
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
-- Wrong type induced from non-recursive term
WITH RECURSIVE foo(i) AS
(SELECT i FROM (VALUES(1),(2)) t(i)
UNION ALL
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
SELECT * FROM foo;
ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
^
HINT: Cast the output of the non-recursive term to the correct type.
-- rejects different typmod, too (should we allow this?)
WITH RECURSIVE foo(i) AS
(SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
UNION ALL
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
SELECT * FROM foo;
ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
^
HINT: Cast the output of the non-recursive term to the correct type.
-- disallow OLD/NEW reference in CTE
CREATE TEMPORARY TABLE x (n integer);
CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
ERROR: cannot refer to OLD within WITH query
--
-- test for bug #4902
--
with cte(foo) as ( values(42) ) values((select foo from cte));
column1
---------
42
(1 row)
with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
foo
-----
42
(1 row)
-- test CTE referencing an outer-level variable (to see that changed-parameter
-- signaling still works properly after fixing this bug)
select ( with cte(foo) as ( values(f1) )
select (select foo from cte) )
from int4_tbl;
foo
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
select ( with cte(foo) as ( values(f1) )
values((select foo from cte)) )
from int4_tbl;
column1
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
--
-- test for nested-recursive-WITH bug
--
WITH RECURSIVE t(j) AS (
WITH RECURSIVE s(i) AS (
VALUES (1)
UNION ALL
SELECT i+1 FROM s WHERE i < 10
)
SELECT i FROM s
UNION ALL
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
j
----
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
3
4
5
6
7
8
9
10
4
5
6
7
8
9
10
5
6
7
8
9
10
6
7
8
9
10
7
8
9
10
8
9
10
9
10
10
(55 rows)
--
-- test WITH attached to intermediate-level set operation
--
WITH outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3)
)
SELECT * FROM outermost ORDER BY 1;
x
---
1
2
3
(3 rows)
WITH outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM outermost -- fail
UNION SELECT * FROM innermost)
)
SELECT * FROM outermost ORDER BY 1;
ERROR: relation "outermost" does not exist
LINE 4: SELECT * FROM outermost
^
DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
WITH RECURSIVE outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM outermost
UNION SELECT * FROM innermost)
)
SELECT * FROM outermost ORDER BY 1;
x
---
1
2
(2 rows)
WITH RECURSIVE outermost(x) AS (
WITH innermost as (SELECT 2 FROM outermost) -- fail
SELECT * FROM innermost
UNION SELECT * from outermost
)
SELECT * FROM outermost ORDER BY 1;
ERROR: recursive reference to query "outermost" must not appear within a subquery
LINE 2: WITH innermost as (SELECT 2 FROM outermost)
^
--
-- This test will fail with the old implementation of PARAM_EXEC parameter
-- assignment, because the "q1" Var passed down to A's targetlist subselect
-- looks exactly like the "A.id" Var passed down to C's subselect, causing
-- the old code to give them the same runtime PARAM_EXEC slot. But the
-- lifespans of the two parameters overlap, thanks to B also reading A.
--
with
A as ( select q2 as id, (select q1) as x from int8_tbl ),
B as ( select id, row_number() over (partition by id) as r from A ),
C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
select * from C;
id | array
-------------------+-------------------------------------
456 | {456}
4567890123456789 | {4567890123456789,4567890123456789}
123 | {123}
4567890123456789 | {4567890123456789,4567890123456789}
-4567890123456789 | {-4567890123456789}
(5 rows)
--
-- Test CTEs read in non-initialization orders
--
WITH RECURSIVE
tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION ALL (
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
WHERE e.row_type = 'false'
)
SELECT * FROM first_remaining
UNION ALL SELECT * FROM effect
)
)
SELECT * FROM iter;
id_key | row_type | link
--------+----------+------
0 | base | 17
1 | true | 17
2 | true | 17
3 | true | 17
4 | true | 17
5 | true | 17
6 | true | 17
(7 rows)
WITH RECURSIVE
tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION (
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
WHERE e.row_type = 'false'
)
SELECT * FROM first_remaining
UNION ALL SELECT * FROM effect
)
)
SELECT * FROM iter;
id_key | row_type | link
--------+----------+------
0 | base | 17
1 | true | 17
2 | true | 17
3 | true | 17
4 | true | 17
5 | true | 17
6 | true | 17
(7 rows)
--
-- Data-modifying statements in WITH
--
-- INSERT ... RETURNING
WITH t AS (
INSERT INTO y
VALUES
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20)
RETURNING *
)
SELECT * FROM t;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)
-- UPDATE ... RETURNING
WITH t AS (
UPDATE y
SET a=a+1
RETURNING *
)
SELECT * FROM t;
a
----
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(20 rows)
SELECT * FROM y;
a
----
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(20 rows)
-- DELETE ... RETURNING
WITH t AS (
DELETE FROM y
WHERE a <= 10
RETURNING *
)
SELECT * FROM t;
a
----
2
3
4
5
6
7
8
9
10
(9 rows)
SELECT * FROM y;
a
----
11
12
13
14
15
16
17
18
19
20
21
(11 rows)
-- forward reference
WITH RECURSIVE t AS (
INSERT INTO y
SELECT a+5 FROM t2 WHERE a > 5
RETURNING *
), t2 AS (
UPDATE y SET a=a-11 RETURNING *
)
SELECT * FROM t
UNION ALL
SELECT * FROM t2;
a
----
11
12
13
14
15
0
1
2
3
4
5
6
7
8
9
10
(16 rows)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
(16 rows)
-- unconditional DO INSTEAD rule
CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
INSERT INTO y VALUES(42) RETURNING *;
WITH t AS (
DELETE FROM y RETURNING *
)
SELECT * FROM t;
a
----
42
(1 row)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
42
(17 rows)
DROP RULE y_rule ON y;
-- check merging of outer CTE with CTE in a rule action
CREATE TEMP TABLE bug6051 AS
select i from generate_series(1,3) as t(i);
SELECT * FROM bug6051;
i
---
1
2
3
(3 rows)
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
i
---
1
2
3
(3 rows)
CREATE TEMP TABLE bug6051_2 (i int);
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
INSERT INTO bug6051_2
SELECT NEW.i;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
i
---
(0 rows)
SELECT * FROM bug6051_2;
i
---
1
2
3
(3 rows)
-- a truly recursive CTE in the same list
WITH RECURSIVE t(a) AS (
SELECT 0
UNION ALL
SELECT a+1 FROM t WHERE a+1 < 5
), t2 as (
INSERT INTO y
SELECT * FROM t RETURNING *
)
SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
a
---
0
1
2
3
4
(5 rows)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
42
0
1
2
3
4
(22 rows)
-- data-modifying WITH in a modifying statement
WITH t AS (
DELETE FROM y
WHERE a <= 10
RETURNING *
)
INSERT INTO y SELECT -a FROM t RETURNING *;
a
-----
0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-10
0
-1
-2
-3
-4
(16 rows)
SELECT * FROM y;
a
-----
11
12
13
14
15
42
0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-10
0
-1
-2
-3
-4
(22 rows)
-- check that WITH query is run to completion even if outer query isn't
WITH t AS (
UPDATE y SET a = a * 100 RETURNING *
)
SELECT * FROM t LIMIT 10;
a
------
1100
1200
1300
1400
1500
4200
0
-100
-200
-300
(10 rows)
SELECT * FROM y;
a
-------
1100
1200
1300
1400
1500
4200
0
-100
-200
-300
-400
-500
-600
-700
-800
-900
-1000
0
-100
-200
-300
-400
(22 rows)
-- data-modifying WITH containing INSERT...ON CONFLICT DO UPDATE
CREATE TABLE withz AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
ALTER TABLE withz ADD UNIQUE (k);
WITH t AS (
INSERT INTO withz SELECT i, 'insert'
FROM generate_series(0, 16) i
ON CONFLICT (k) DO UPDATE SET v = withz.v || ', now update'
RETURNING *
)
SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
k | v | a
---+--------+---
0 | insert | 0
0 | insert | 0
(2 rows)
-- Test EXCLUDED.* reference within CTE
WITH aa AS (
INSERT INTO withz VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v
WHERE withz.k != EXCLUDED.k
RETURNING *
)
SELECT * FROM aa;
k | v
---+---
(0 rows)
-- New query/snapshot demonstrates side-effects of previous query.
SELECT * FROM withz ORDER BY k;
k | v
----+------------------
0 | insert
1 | 1 v, now update
2 | insert
3 | insert
4 | 4 v, now update
5 | insert
6 | insert
7 | 7 v, now update
8 | insert
9 | insert
10 | 10 v, now update
11 | insert
12 | insert
13 | 13 v, now update
14 | insert
15 | insert
16 | 16 v, now update
(17 rows)
--
-- Ensure subqueries within the update clause work, even if they
-- reference outside values
--
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE withz.k = (SELECT a FROM aa);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 ))
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
-- Update a row more than once, in different parts of a wCTE. That is
-- an allowed, presumably very rare, edge case, but since it was
-- broken in the past, having a test seems worthwhile.
WITH simpletup AS (
SELECT 2 k, 'Green' v),
upsert_cte AS (
INSERT INTO withz VALUES(2, 'Blue') ON CONFLICT (k) DO
UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = withz.k)
RETURNING k, v)
INSERT INTO withz VALUES(2, 'Red') ON CONFLICT (k) DO
UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = withz.k)
RETURNING k, v;
k | v
---+---
(0 rows)
DROP TABLE withz;
-- check that run to completion happens in proper ordering
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 3);
CREATE TEMPORARY TABLE yy (a INTEGER);
WITH RECURSIVE t1 AS (
INSERT INTO y SELECT * FROM y RETURNING *
), t2 AS (
INSERT INTO yy SELECT * FROM t1 RETURNING *
)
SELECT 1;
?column?
----------
1
(1 row)
SELECT * FROM y;
a
---
1
2
3
1
2
3
(6 rows)
SELECT * FROM yy;
a
---
1
2
3
(3 rows)
WITH RECURSIVE t1 AS (
INSERT INTO yy SELECT * FROM t2 RETURNING *
), t2 AS (
INSERT INTO y SELECT * FROM y RETURNING *
)
SELECT 1;
?column?
----------
1
(1 row)
SELECT * FROM y;
a
---
1
2
3
1
2
3
1
2
3
1
2
3
(12 rows)
SELECT * FROM yy;
a
---
1
2
3
1
2
3
1
2
3
(9 rows)
-- triggers
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 10);
CREATE FUNCTION y_trigger() RETURNS trigger AS $$
begin
raise notice 'y_trigger: a = %', new.a;
return new;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(21),
(22),
(23)
RETURNING *
)
SELECT * FROM t;
NOTICE: y_trigger: a = 21
NOTICE: y_trigger: a = 22
NOTICE: y_trigger: a = 23
a
----
21
22
23
(3 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
(13 rows)
DROP TRIGGER y_trig ON y;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(31),
(32),
(33)
RETURNING *
)
SELECT * FROM t LIMIT 1;
NOTICE: y_trigger: a = 31
NOTICE: y_trigger: a = 32
NOTICE: y_trigger: a = 33
a
----
31
(1 row)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
31
32
33
(16 rows)
DROP TRIGGER y_trig ON y;
CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
begin
raise notice 'y_trigger';
return null;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(41),
(42),
(43)
RETURNING *
)
SELECT * FROM t;
NOTICE: y_trigger
a
----
41
42
43
(3 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
31
32
33
41
42
43
(19 rows)
DROP TRIGGER y_trig ON y;
DROP FUNCTION y_trigger();
-- WITH attached to inherited UPDATE or DELETE
CREATE TEMP TABLE parent ( id int, val text );
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
INSERT INTO parent VALUES ( 1, 'p1' );
INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
UPDATE parent SET id = id + totalid FROM rcte;
SELECT * FROM parent;
id | val
----+-----
72 | p1
82 | c11
83 | c12
94 | c21
95 | c22
(5 rows)
WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
UPDATE parent SET id = id + newid FROM wcte;
SELECT * FROM parent;
id | val
-----+-----
114 | p1
42 | new
124 | c11
125 | c12
136 | c21
137 | c22
(6 rows)
WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
DELETE FROM parent USING rcte WHERE id = maxid;
SELECT * FROM parent;
id | val
-----+-----
114 | p1
42 | new
124 | c11
125 | c12
136 | c21
(5 rows)
WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
DELETE FROM parent USING wcte WHERE id = newid;
SELECT * FROM parent;
id | val
-----+------
114 | p1
124 | c11
125 | c12
136 | c21
42 | new2
(5 rows)
-- check EXPLAIN VERBOSE for a wCTE with RETURNING
EXPLAIN (VERBOSE, COSTS OFF)
WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
DELETE FROM a USING wcte WHERE aa = q2;
QUERY PLAN
----------------------------------------------------
Delete on public.a
Delete on public.a
Delete on public.b
Delete on public.c
Delete on public.d
CTE wcte
-> Insert on public.int8_tbl
Output: int8_tbl.q2
-> Result
Output: '42'::bigint, '47'::bigint
-> Nested Loop
Output: a.ctid, wcte.*
Join Filter: (a.aa = wcte.q2)
-> Seq Scan on public.a
Output: a.ctid, a.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: b.ctid, wcte.*
Join Filter: (b.aa = wcte.q2)
-> Seq Scan on public.b
Output: b.ctid, b.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: c.ctid, wcte.*
Join Filter: (c.aa = wcte.q2)
-> Seq Scan on public.c
Output: c.ctid, c.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: d.ctid, wcte.*
Join Filter: (d.aa = wcte.q2)
-> Seq Scan on public.d
Output: d.ctid, d.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
(38 rows)
-- error cases
-- data-modifying WITH tries to use its own output
WITH RECURSIVE t AS (
INSERT INTO y
SELECT * FROM t
)
VALUES(FALSE);
ERROR: recursive query "t" must not contain data-modifying statements
LINE 1: WITH RECURSIVE t AS (
^
-- no RETURNING in a referenced data-modifying WITH
WITH t AS (
INSERT INTO y VALUES(0)
)
SELECT * FROM t;
ERROR: WITH query "t" does not have a RETURNING clause
LINE 4: SELECT * FROM t;
^
-- data-modifying WITH allowed only at the top level
SELECT * FROM (
WITH t AS (UPDATE y SET a=a+1 RETURNING *)
SELECT * FROM t
) ss;
ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
^
-- most variants of rules aren't allowed
CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
WITH t AS (
INSERT INTO y VALUES(0)
)
VALUES(FALSE);
ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
DROP RULE y_rule ON y;
-- check that parser lookahead for WITH doesn't cause any odd behavior
create table foo (with baz); -- fail, WITH is a reserved word
ERROR: syntax error at or near "with"
LINE 1: create table foo (with baz);
^
create table foo (with ordinality); -- fail, WITH is a reserved word
ERROR: syntax error at or near "with"
LINE 1: create table foo (with ordinality);
^
with ordinality as (select 1 as x) select * from ordinality;
x
---
1
(1 row)
-- check sane response to attempt to modify CTE relation
WITH test AS (SELECT 42) INSERT INTO test VALUES (1);
ERROR: relation "test" does not exist
LINE 1: WITH test AS (SELECT 42) INSERT INTO test VALUES (1);
^
-- check response to attempt to modify table with same name as a CTE (perhaps
-- surprisingly it works, because CTEs don't hide tables from data-modifying
-- statements)
create temp table test (i int);
with test as (select 42) insert into test select * from test;
select * from test;
i
----
42
(1 row)
drop table test;
--
-- WINDOW FUNCTIONS
--
CREATE TEMPORARY TABLE empsalary (
depname varchar,
empno bigint,
salary int,
enroll_date date
);
INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
depname | empno | salary | sum
-----------+-------+--------+-------
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
develop | 11 | 5200 | 25100
develop | 10 | 5200 | 25100
develop | 8 | 6000 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
sales | 3 | 4800 | 14600
sales | 4 | 4800 | 14600
sales | 1 | 5000 | 14600
(10 rows)
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 7 | 4200 | 1
develop | 9 | 4500 | 2
develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
develop | 8 | 6000 | 5
personnel | 5 | 3500 | 1
personnel | 2 | 3900 | 2
sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
sales | 1 | 5000 | 3
(10 rows)
-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;
four | ten | sum | avg
------+-----+------+------------------------
0 | 0 | 0 | 0.00000000000000000000
0 | 2 | 0 | 2.0000000000000000
0 | 4 | 0 | 4.0000000000000000
0 | 6 | 0 | 6.0000000000000000
0 | 8 | 0 | 8.0000000000000000
1 | 1 | 2500 | 1.00000000000000000000
1 | 3 | 2500 | 3.0000000000000000
1 | 5 | 2500 | 5.0000000000000000
1 | 7 | 2500 | 7.0000000000000000
1 | 9 | 2500 | 9.0000000000000000
2 | 0 | 5000 | 0.00000000000000000000
2 | 2 | 5000 | 2.0000000000000000
2 | 4 | 5000 | 4.0000000000000000
2 | 6 | 5000 | 6.0000000000000000
2 | 8 | 5000 | 8.0000000000000000
3 | 1 | 7500 | 1.00000000000000000000
3 | 3 | 7500 | 3.0000000000000000
3 | 5 | 7500 | 5.0000000000000000
3 | 7 | 7500 | 7.0000000000000000
3 | 9 | 7500 | 9.0000000000000000
(20 rows)
SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
depname | empno | salary | sum
-----------+-------+--------+-------
develop | 11 | 5200 | 25100
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
develop | 8 | 6000 | 25100
develop | 10 | 5200 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
sales | 3 | 4800 | 14600
sales | 1 | 5000 | 14600
sales | 4 | 4800 | 14600
(10 rows)
SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 7 | 4200 | 1
personnel | 5 | 3500 | 1
sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
personnel | 2 | 3900 | 2
develop | 9 | 4500 | 2
sales | 1 | 5000 | 3
develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
develop | 8 | 6000 | 5
(10 rows)
-- empty window specification
SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
count
-------
10
10
10
10
10
10
10
10
10
10
(10 rows)
SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
count
-------
10
10
10
10
10
10
10
10
10
10
(10 rows)
-- no window operation
SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
four
------
(0 rows)
-- cumulative aggregate
SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
sum_1 | ten | four
-------+-----+------
0 | 0 | 0
0 | 0 | 0
2 | 0 | 2
3 | 1 | 3
4 | 1 | 1
5 | 1 | 1
3 | 3 | 3
0 | 4 | 0
1 | 7 | 1
1 | 9 | 1
(10 rows)
SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
row_number
------------
1
2
3
4
5
6
7
8
9
10
(10 rows)
SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
rank_1 | ten | four
--------+-----+------
1 | 0 | 0
1 | 0 | 0
3 | 4 | 0
1 | 1 | 1
1 | 1 | 1
3 | 7 | 1
4 | 9 | 1
1 | 0 | 2
1 | 1 | 3
2 | 3 | 3
(10 rows)
SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
dense_rank | ten | four
------------+-----+------
1 | 0 | 0
1 | 0 | 0
2 | 4 | 0
1 | 1 | 1
1 | 1 | 1
2 | 7 | 1
3 | 9 | 1
1 | 0 | 2
1 | 1 | 3
2 | 3 | 3
(10 rows)
SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
percent_rank | ten | four
-------------------+-----+------
0 | 0 | 0
0 | 0 | 0
1 | 4 | 0
0 | 1 | 1
0 | 1 | 1
0.666666666666667 | 7 | 1
1 | 9 | 1
0 | 0 | 2
0 | 1 | 3
1 | 3 | 3
(10 rows)
SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
cume_dist | ten | four
-------------------+-----+------
0.666666666666667 | 0 | 0
0.666666666666667 | 0 | 0
1 | 4 | 0
0.5 | 1 | 1
0.5 | 1 | 1
0.75 | 7 | 1
1 | 9 | 1
1 | 0 | 2
0.5 | 1 | 3
1 | 3 | 3
(10 rows)
SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
ntile | ten | four
-------+-----+------
1 | 0 | 0
1 | 0 | 0
1 | 0 | 2
1 | 1 | 1
2 | 1 | 1
2 | 1 | 3
2 | 3 | 3
3 | 4 | 0
3 | 7 | 1
3 | 9 | 1
(10 rows)
SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
ntile | ten | four
-------+-----+------
| 0 | 0
| 0 | 0
(2 rows)
SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lag | ten | four
-----+-----+------
| 0 | 0
0 | 0 | 0
0 | 4 | 0
| 1 | 1
1 | 1 | 1
1 | 7 | 1
7 | 9 | 1
| 0 | 2
| 1 | 3
1 | 3 | 3
(10 rows)
SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lag | ten | four
-----+-----+------
0 | 0 | 0
0 | 0 | 0
4 | 4 | 0
| 1 | 1
1 | 1 | 1
1 | 7 | 1
7 | 9 | 1
| 0 | 2
| 1 | 3
| 3 | 3
(10 rows)
SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lag | ten | four
-----+-----+------
0 | 0 | 0
0 | 0 | 0
4 | 4 | 0
0 | 1 | 1
1 | 1 | 1
1 | 7 | 1
7 | 9 | 1
0 | 0 | 2
0 | 1 | 3
0 | 3 | 3
(10 rows)
SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lead | ten | four
------+-----+------
0 | 0 | 0
4 | 0 | 0
| 4 | 0
1 | 1 | 1
7 | 1 | 1
9 | 7 | 1
| 9 | 1
| 0 | 2
3 | 1 | 3
| 3 | 3
(10 rows)
SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lead | ten | four
------+-----+------
0 | 0 | 0
8 | 0 | 0
| 4 | 0
2 | 1 | 1
14 | 1 | 1
18 | 7 | 1
| 9 | 1
| 0 | 2
6 | 1 | 3
| 3 | 3
(10 rows)
SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lead | ten | four
------+-----+------
0 | 0 | 0
8 | 0 | 0
-1 | 4 | 0
2 | 1 | 1
14 | 1 | 1
18 | 7 | 1
-1 | 9 | 1
-1 | 0 | 2
6 | 1 | 3
-1 | 3 | 3
(10 rows)
SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
first_value | ten | four
-------------+-----+------
0 | 0 | 0
0 | 0 | 0
0 | 4 | 0
1 | 1 | 1
1 | 1 | 1
1 | 7 | 1
1 | 9 | 1
0 | 0 | 2
1 | 1 | 3
1 | 3 | 3
(10 rows)
-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
last_value | ten | four
------------+-----+------
0 | 0 | 0
0 | 0 | 2
0 | 0 | 0
1 | 1 | 1
1 | 1 | 3
1 | 1 | 1
3 | 3 | 3
0 | 4 | 0
1 | 7 | 1
1 | 9 | 1
(10 rows)
SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
(SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
ORDER BY four, ten;
last_value | ten | four
------------+-----+------
4 | 0 | 0
4 | 0 | 0
4 | 4 | 0
9 | 1 | 1
9 | 1 | 1
9 | 7 | 1
9 | 9 | 1
0 | 0 | 2
3 | 1 | 3
3 | 3 | 3
(10 rows)
SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
nth_value | ten | four
-----------+-----+------
0 | 0 | 0
0 | 0 | 0
0 | 4 | 0
1 | 1 | 1
1 | 1 | 1
1 | 7 | 1
1 | 9 | 1
| 0 | 2
| 1 | 3
| 3 | 3
(10 rows)
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;
ten | two | gsum | wsum
-----+-----+-------+--------
0 | 0 | 45000 | 45000
2 | 0 | 47000 | 92000
4 | 0 | 49000 | 141000
6 | 0 | 51000 | 192000
8 | 0 | 53000 | 245000
1 | 1 | 46000 | 46000
3 | 1 | 48000 | 94000
5 | 1 | 50000 | 144000
7 | 1 | 52000 | 196000
9 | 1 | 54000 | 250000
(10 rows)
SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
count | four
-------+------
4 | 1
4 | 1
4 | 1
4 | 1
2 | 3
2 | 3
(6 rows)
SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
FROM tenk1 WHERE unique2 < 10;
cntsum
--------
22
22
87
24
24
82
92
51
92
136
(10 rows)
-- opexpr with different windows evaluation.
SELECT * FROM(
SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
FROM tenk1
)sub
WHERE total <> fourcount + twosum;
total | fourcount | twosum
-------+-----------+--------
(0 rows)
SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
avg
------------------------
0.00000000000000000000
0.00000000000000000000
0.00000000000000000000
1.00000000000000000000
1.00000000000000000000
1.00000000000000000000
1.00000000000000000000
2.0000000000000000
3.0000000000000000
3.0000000000000000
(10 rows)
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
ten | two | gsum | wsum
-----+-----+-------+--------
0 | 0 | 45000 | 45000
2 | 0 | 47000 | 92000
4 | 0 | 49000 | 141000
6 | 0 | 51000 | 192000
8 | 0 | 53000 | 245000
1 | 1 | 46000 | 46000
3 | 1 | 48000 | 94000
5 | 1 | 50000 | 144000
7 | 1 | 52000 | 196000
9 | 1 | 54000 | 250000
(10 rows)
-- more than one window with GROUP BY
SELECT sum(salary),
row_number() OVER (ORDER BY depname),
sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;
sum | row_number | sum
-------+------------+-------
14600 | 3 | 14600
7400 | 2 | 22000
25100 | 1 | 47100
(3 rows)
-- identical windows with different names
SELECT sum(salary) OVER w1, count(*) OVER w2
FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
sum | count
-------+-------
3500 | 1
7400 | 2
11600 | 3
16100 | 4
25700 | 6
25700 | 6
30700 | 7
41100 | 9
41100 | 9
47100 | 10
(10 rows)
-- subplan
SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
FROM tenk1 s WHERE unique2 < 10;
lead
------
0
0
4
1
7
9
0
3
(10 rows)
-- empty table
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
count
-------
(0 rows)
-- mixture of agg/wfunc in the same window
SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
sum | rank
-------+------
6000 | 1
16400 | 2
16400 | 2
20900 | 4
25100 | 5
3900 | 1
7400 | 2
5000 | 1
14600 | 2
14600 | 2
(10 rows)
-- strict aggs
SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
SELECT *,
CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
CASE WHEN
AVG(salary) OVER (PARTITION BY depname) < salary
THEN 200 END AS depadj FROM empsalary
)s;
empno | depname | salary | bonus | depadj | min | max
-------+-----------+--------+-------+--------+------+-----
1 | sales | 5000 | 1000 | 200 | 1000 | 200
2 | personnel | 3900 | 1000 | 200 | 1000 | 200
3 | sales | 4800 | 500 | | 500 | 200
4 | sales | 4800 | 500 | | 500 | 200
5 | personnel | 3500 | 500 | | 500 | 200
7 | develop | 4200 | | | 500 | 200
8 | develop | 6000 | 1000 | 200 | 500 | 200
9 | develop | 4500 | | | 500 | 200
10 | develop | 5200 | 500 | 200 | 500 | 200
11 | develop | 5200 | 500 | 200 | 500 | 200
(10 rows)
-- window function over ungrouped agg over empty row set (bug before 9.1)
SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;
sum
-----
0
(1 row)
-- window function with ORDER BY an expression involving aggregates (9.1 bug)
select ten,
sum(unique1) + sum(unique2) as res,
rank() over (order by sum(unique1) + sum(unique2)) as rank
from tenk1
group by ten order by ten;
ten | res | rank
-----+----------+------
0 | 9976146 | 4
1 | 10114187 | 9
2 | 10059554 | 8
3 | 9878541 | 1
4 | 9881005 | 2
5 | 9981670 | 5
6 | 9947099 | 3
7 | 10120309 | 10
8 | 9991305 | 6
9 | 10040184 | 7
(10 rows)
-- window and aggregate with GROUP BY expression (9.2 bug)
explain (costs off)
select first_value(max(x)) over (), y
from (select unique1 as x, ten+four as y from tenk1) ss
group by y;
QUERY PLAN
---------------------------------------------
WindowAgg
-> HashAggregate
Group Key: (tenk1.ten + tenk1.four)
-> Seq Scan on tenk1
(4 rows)
-- test non-default frame specifications
SELECT four, ten,
sum(ten) over (partition by four order by ten),
last_value(ten) over (partition by four order by ten)
FROM (select distinct ten, four from tenk1) ss;
four | ten | sum | last_value
------+-----+-----+------------
0 | 0 | 0 | 0
0 | 2 | 2 | 2
0 | 4 | 6 | 4
0 | 6 | 12 | 6
0 | 8 | 20 | 8
1 | 1 | 1 | 1
1 | 3 | 4 | 3
1 | 5 | 9 | 5
1 | 7 | 16 | 7
1 | 9 | 25 | 9
2 | 0 | 0 | 0
2 | 2 | 2 | 2
2 | 4 | 6 | 4
2 | 6 | 12 | 6
2 | 8 | 20 | 8
3 | 1 | 1 | 1
3 | 3 | 4 | 3
3 | 5 | 9 | 5
3 | 7 | 16 | 7
3 | 9 | 25 | 9
(20 rows)
SELECT four, ten,
sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
four | ten | sum | last_value
------+-----+-----+------------
0 | 0 | 0 | 0
0 | 2 | 2 | 2
0 | 4 | 6 | 4
0 | 6 | 12 | 6
0 | 8 | 20 | 8
1 | 1 | 1 | 1
1 | 3 | 4 | 3
1 | 5 | 9 | 5
1 | 7 | 16 | 7
1 | 9 | 25 | 9
2 | 0 | 0 | 0
2 | 2 | 2 | 2
2 | 4 | 6 | 4
2 | 6 | 12 | 6
2 | 8 | 20 | 8
3 | 1 | 1 | 1
3 | 3 | 4 | 3
3 | 5 | 9 | 5
3 | 7 | 16 | 7
3 | 9 | 25 | 9
(20 rows)
SELECT four, ten,
sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
FROM (select distinct ten, four from tenk1) ss;
four | ten | sum | last_value
------+-----+-----+------------
0 | 0 | 20 | 8
0 | 2 | 20 | 8
0 | 4 | 20 | 8
0 | 6 | 20 | 8
0 | 8 | 20 | 8
1 | 1 | 25 | 9
1 | 3 | 25 | 9
1 | 5 | 25 | 9
1 | 7 | 25 | 9
1 | 9 | 25 | 9
2 | 0 | 20 | 8
2 | 2 | 20 | 8
2 | 4 | 20 | 8
2 | 6 | 20 | 8
2 | 8 | 20 | 8
3 | 1 | 25 | 9
3 | 3 | 25 | 9
3 | 5 | 25 | 9
3 | 7 | 25 | 9
3 | 9 | 25 | 9
(20 rows)
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
four | two | sum | last_value
------+-----+-----+------------
0 | 0 | 0 | 0
0 | 0 | 0 | 0
0 | 1 | 2 | 1
0 | 1 | 2 | 1
0 | 2 | 4 | 2
1 | 0 | 0 | 0
1 | 0 | 0 | 0
1 | 1 | 2 | 1
1 | 1 | 2 | 1
1 | 2 | 4 | 2
2 | 0 | 0 | 0
2 | 0 | 0 | 0
2 | 1 | 2 | 1
2 | 1 | 2 | 1
2 | 2 | 4 | 2
3 | 0 | 0 | 0
3 | 0 | 0 | 0
3 | 1 | 2 | 1
3 | 1 | 2 | 1
3 | 2 | 4 | 2
(20 rows)
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
four | two | sum | last_value
------+-----+-----+------------
0 | 0 | 0 | 0
0 | 0 | 0 | 0
0 | 1 | 1 | 1
0 | 1 | 2 | 1
0 | 2 | 4 | 2
1 | 0 | 0 | 0
1 | 0 | 0 | 0
1 | 1 | 1 | 1
1 | 1 | 2 | 1
1 | 2 | 4 | 2
2 | 0 | 0 | 0
2 | 0 | 0 | 0
2 | 1 | 1 | 1
2 | 1 | 2 | 1
2 | 2 | 4 | 2
3 | 0 | 0 | 0
3 | 0 | 0 | 0
3 | 1 | 1 | 1
3 | 1 | 2 | 1
3 | 2 | 4 | 2
(20 rows)
SELECT sum(unique1) over (order by four range between current row and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
45 | 0 | 0
45 | 8 | 0
45 | 4 | 0
33 | 5 | 1
33 | 9 | 1
33 | 1 | 1
18 | 6 | 2
18 | 2 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
SELECT sum(unique1) over (rows between current row and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
45 | 4 | 0
41 | 2 | 2
39 | 1 | 1
38 | 6 | 2
32 | 9 | 1
23 | 8 | 0
15 | 5 | 1
10 | 3 | 3
7 | 7 | 3
0 | 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 2 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
7 | 4 | 0
13 | 2 | 2
22 | 1 | 1
26 | 6 | 2
29 | 9 | 1
31 | 8 | 0
32 | 5 | 1
23 | 3 | 3
15 | 7 | 3
10 | 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
7 | 4 | 0
13 | 2 | 2
22 | 1 | 1
26 | 6 | 2
29 | 9 | 1
31 | 8 | 0
32 | 5 | 1
23 | 3 | 3
15 | 7 | 3
10 | 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
3 | 4 | 0
11 | 2 | 2
21 | 1 | 1
20 | 6 | 2
20 | 9 | 1
23 | 8 | 0
27 | 5 | 1
20 | 3 | 3
8 | 7 | 3
10 | 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 4 | 0
| 2 | 2
| 1 | 1
| 6 | 2
| 9 | 1
| 8 | 0
| 5 | 1
| 3 | 3
| 7 | 3
| 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
4 | 4 | 0
2 | 2 | 2
1 | 1 | 1
6 | 6 | 2
9 | 9 | 1
8 | 8 | 0
5 | 5 | 1
3 | 3 | 3
7 | 7 | 3
0 | 0 | 0
(10 rows)
SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row),
unique1, four
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
8 | 0 | 0
4 | 8 | 0
5 | 4 | 0
9 | 5 | 1
1 | 9 | 1
6 | 1 | 1
2 | 6 | 2
3 | 2 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
| 0 | 0
5 | 8 | 0
5 | 4 | 0
| 5 | 1
6 | 9 | 1
6 | 1 | 1
3 | 6 | 2
3 | 2 | 2
| 3 | 3
| 7 | 3
(10 rows)
SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
0 | 0 | 0
8 | 8 | 0
4 | 4 | 0
5 | 5 | 1
9 | 9 | 1
1 | 1 | 1
6 | 6 | 2
2 | 2 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row),
unique1, four
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
4 | 0 | 0
5 | 8 | 0
9 | 4 | 0
1 | 5 | 1
6 | 9 | 1
2 | 1 | 1
3 | 6 | 2
7 | 2 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
| 0 | 0
5 | 8 | 0
9 | 4 | 0
| 5 | 1
6 | 9 | 1
2 | 1 | 1
3 | 6 | 2
7 | 2 | 2
| 3 | 3
| 7 | 3
(10 rows)
SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
0 | 0 | 0
5 | 8 | 0
9 | 4 | 0
5 | 5 | 1
6 | 9 | 1
2 | 1 | 1
3 | 6 | 2
7 | 2 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 4 | 0
4 | 2 | 2
6 | 1 | 1
3 | 6 | 2
7 | 9 | 1
15 | 8 | 0
17 | 5 | 1
13 | 3 | 3
8 | 7 | 3
10 | 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between 1 following and 3 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
9 | 4 | 0
16 | 2 | 2
23 | 1 | 1
22 | 6 | 2
16 | 9 | 1
15 | 8 | 0
10 | 5 | 1
7 | 3 | 3
0 | 7 | 3
| 0 | 0
(10 rows)
SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
6 | 4 | 0
7 | 2 | 2
13 | 1 | 1
22 | 6 | 2
30 | 9 | 1
35 | 8 | 0
38 | 5 | 1
45 | 3 | 3
45 | 7 | 3
45 | 0 | 0
(10 rows)
SELECT sum(unique1) over (w range between current row and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
45 | 0 | 0
45 | 8 | 0
45 | 4 | 0
33 | 5 | 1
33 | 9 | 1
33 | 1 | 1
18 | 6 | 2
18 | 2 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row),
unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
12 | 0 | 0
4 | 8 | 0
8 | 4 | 0
22 | 5 | 1
18 | 9 | 1
26 | 1 | 1
29 | 6 | 2
33 | 2 | 2
42 | 3 | 3
38 | 7 | 3
(10 rows)
SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
35 | 3 | 3
35 | 7 | 3
(10 rows)
SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
0 | 0 | 0
8 | 8 | 0
4 | 4 | 0
17 | 5 | 1
21 | 9 | 1
13 | 1 | 1
33 | 6 | 2
29 | 2 | 2
38 | 3 | 3
42 | 7 | 3
(10 rows)
SELECT first_value(unique1) over w,
nth_value(unique1, 2) over w AS nth_2,
last_value(unique1) over w, unique1, four
FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);
first_value | nth_2 | last_value | unique1 | four
-------------+-------+------------+---------+------
0 | 8 | 7 | 0 | 0
0 | 8 | 7 | 8 | 0
0 | 8 | 7 | 4 | 0
5 | 9 | 7 | 5 | 1
5 | 9 | 7 | 9 | 1
5 | 9 | 7 | 1 | 1
6 | 2 | 7 | 6 | 2
6 | 2 | 7 | 2 | 2
3 | 7 | 7 | 3 | 3
3 | 7 | 7 | 7 | 3
(10 rows)
SELECT sum(unique1) over
(order by unique1
rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING),
unique1
FROM tenk1 WHERE unique1 < 10;
sum | unique1
-----+---------
0 | 0
1 | 1
3 | 2
5 | 3
7 | 4
9 | 5
11 | 6
13 | 7
15 | 8
17 | 9
(10 rows)
CREATE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows
FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
6 | 18
7 | 21
8 | 24
9 | 27
10 | 19
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
---------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
CREATE OR REPLACE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
exclude current row) as sum_rows FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 2
2 | 4
3 | 6
4 | 8
5 | 10
6 | 12
7 | 14
8 | 16
9 | 18
10 | 9
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
-----------------------------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
CREATE OR REPLACE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
exclude group) as sum_rows FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 2
2 | 4
3 | 6
4 | 8
5 | 10
6 | 12
7 | 14
8 | 16
9 | 18
10 | 9
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
-----------------------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
CREATE OR REPLACE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
exclude ties) as sum_rows FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
6 | 18
7 | 21
8 | 24
9 | 27
10 | 19
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
----------------------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
CREATE OR REPLACE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
exclude no others) as sum_rows FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
6 | 18
7 | 21
8 | 24
9 | 27
10 | 19
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
---------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
CREATE OR REPLACE TEMP VIEW v_window AS
SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i;
SELECT * FROM v_window;
i | sum_rows
----+----------
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
6 | 18
7 | 21
8 | 24
9 | 27
10 | 19
(10 rows)
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
-----------------------------------------------------------------------------------------
SELECT i.i, +
sum(i.i) OVER (ORDER BY i.i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
FROM generate_series(1, 10) i(i);
(1 row)
DROP VIEW v_window;
CREATE TEMP VIEW v_window AS
SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i
FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i;
SELECT pg_get_viewdef('v_window');
pg_get_viewdef
---------------------------------------------------------------------------------------------------------------------------
SELECT i.i, +
min(i.i) OVER (ORDER BY i.i RANGE BETWEEN '@ 1 day'::interval PRECEDING AND '@ 10 days'::interval FOLLOWING) AS min_i+
FROM generate_series(now(), (now() + '@ 100 days'::interval), '@ 1 hour'::interval) i(i);
(1 row)
-- RANGE offset PRECEDING/FOLLOWING tests
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 3 | 3
| 7 | 3
10 | 6 | 2
10 | 2 | 2
18 | 9 | 1
18 | 5 | 1
18 | 1 | 1
23 | 0 | 0
23 | 8 | 0
23 | 4 | 0
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
33 | 0 | 0
41 | 8 | 0
37 | 4 | 0
35 | 5 | 1
39 | 9 | 1
31 | 1 | 1
43 | 6 | 2
39 | 2 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
33 | 0 | 0
33 | 8 | 0
33 | 4 | 0
30 | 5 | 1
30 | 9 | 1
30 | 1 | 1
37 | 6 | 2
37 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
4 | 0 | 0
12 | 4 | 0
12 | 8 | 0
6 | 1 | 1
15 | 5 | 1
14 | 9 | 1
8 | 2 | 2
8 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following
exclude current row),unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
4 | 0 | 0
8 | 4 | 0
4 | 8 | 0
5 | 1 | 1
10 | 5 | 1
5 | 9 | 1
6 | 2 | 2
2 | 6 | 2
7 | 3 | 3
3 | 7 | 3
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following),
salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
32200 | 4500 | 01-01-2008
32200 | 4200 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following),
salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
32200 | 4200 | 01-01-2008
32200 | 4500 | 01-01-2008
36100 | 3500 | 12-10-2007
47100 | 5200 | 08-15-2007
47100 | 4800 | 08-08-2007
47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
38400 | 3900 | 12-23-2006
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
(10 rows)
select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following),
salary, enroll_date from empsalary;
sum | salary | enroll_date
-----+--------+-------------
| 4200 | 01-01-2008
| 4500 | 01-01-2008
| 3500 | 12-10-2007
| 5200 | 08-15-2007
| 4800 | 08-08-2007
| 4800 | 08-01-2007
| 5200 | 08-01-2007
| 3900 | 12-23-2006
| 5000 | 10-01-2006
| 6000 | 10-01-2006
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
exclude current row), salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
29900 | 5000 | 10-01-2006
28900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
42300 | 4800 | 08-01-2007
41900 | 5200 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
27700 | 4500 | 01-01-2008
28000 | 4200 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
exclude group), salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
23900 | 5000 | 10-01-2006
23900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
37100 | 4800 | 08-01-2007
37100 | 5200 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
23500 | 4500 | 01-01-2008
23500 | 4200 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
exclude ties), salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
28900 | 5000 | 10-01-2006
29900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
41900 | 4800 | 08-01-2007
42300 | 5200 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
28000 | 4500 | 01-01-2008
27700 | 4200 | 01-01-2008
(10 rows)
select first_value(salary) over(order by salary range between 1000 preceding and 1000 following),
lead(salary) over(order by salary range between 1000 preceding and 1000 following),
nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following),
salary from empsalary;
first_value | lead | nth_value | salary
-------------+------+-----------+--------
3500 | 3900 | 3500 | 3500
3500 | 4200 | 3500 | 3900
3500 | 4500 | 3500 | 4200
3500 | 4800 | 3500 | 4500
3900 | 4800 | 3900 | 4800
3900 | 5000 | 3900 | 4800
4200 | 5200 | 4200 | 5000
4200 | 5200 | 4200 | 5200
4200 | 6000 | 4200 | 5200
5000 | | 5000 | 6000
(10 rows)
select last_value(salary) over(order by salary range between 1000 preceding and 1000 following),
lag(salary) over(order by salary range between 1000 preceding and 1000 following),
salary from empsalary;
last_value | lag | salary
------------+------+--------
4500 | | 3500
4800 | 3500 | 3900
5200 | 3900 | 4200
5200 | 4200 | 4500
5200 | 4500 | 4800
5200 | 4800 | 4800
6000 | 4800 | 5000
6000 | 5000 | 5200
6000 | 5200 | 5200
6000 | 5200 | 6000
(10 rows)
select first_value(salary) over(order by salary range between 1000 following and 3000 following
exclude current row),
lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties),
nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following
exclude ties),
salary from empsalary;
first_value | lead | nth_value | salary
-------------+------+-----------+--------
4500 | 3900 | 4500 | 3500
5000 | 4200 | 5000 | 3900
5200 | 4500 | 5200 | 4200
6000 | 4800 | 6000 | 4500
6000 | 4800 | 6000 | 4800
6000 | 5000 | 6000 | 4800
6000 | 5200 | 6000 | 5000
| 5200 | | 5200
| 6000 | | 5200
| | | 6000
(10 rows)
select last_value(salary) over(order by salary range between 1000 following and 3000 following
exclude group),
lag(salary) over(order by salary range between 1000 following and 3000 following exclude group),
salary from empsalary;
last_value | lag | salary
------------+------+--------
6000 | | 3500
6000 | 3500 | 3900
6000 | 3900 | 4200
6000 | 4200 | 4500
6000 | 4500 | 4800
6000 | 4800 | 4800
6000 | 4800 | 5000
| 5000 | 5200
| 5200 | 5200
| 5200 | 6000
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude ties),
last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following),
salary, enroll_date from empsalary;
first_value | last_value | salary | enroll_date
-------------+------------+--------+-------------
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
5000 | 4200 | 4800 | 08-01-2007
5000 | 4200 | 5200 | 08-01-2007
5000 | 4200 | 4800 | 08-08-2007
5000 | 4200 | 5200 | 08-15-2007
5000 | 4200 | 3500 | 12-10-2007
5000 | 4200 | 4500 | 01-01-2008
5000 | 4200 | 4200 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude ties),
last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude ties),
salary, enroll_date from empsalary;
first_value | last_value | salary | enroll_date
-------------+------------+--------+-------------
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
5000 | 4200 | 4800 | 08-01-2007
5000 | 4200 | 5200 | 08-01-2007
5000 | 4200 | 4800 | 08-08-2007
5000 | 4200 | 5200 | 08-15-2007
5000 | 4200 | 3500 | 12-10-2007
5000 | 4500 | 4500 | 01-01-2008
5000 | 4200 | 4200 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude group),
last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude group),
salary, enroll_date from empsalary;
first_value | last_value | salary | enroll_date
-------------+------------+--------+-------------
3900 | 5200 | 5000 | 10-01-2006
3900 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
5000 | 4200 | 4800 | 08-01-2007
5000 | 4200 | 5200 | 08-01-2007
5000 | 4200 | 4800 | 08-08-2007
5000 | 4200 | 5200 | 08-15-2007
5000 | 4200 | 3500 | 12-10-2007
5000 | 3500 | 4500 | 01-01-2008
5000 | 3500 | 4200 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude current row),
last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude current row),
salary, enroll_date from empsalary;
first_value | last_value | salary | enroll_date
-------------+------------+--------+-------------
6000 | 5200 | 5000 | 10-01-2006
5000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
5000 | 4200 | 4800 | 08-01-2007
5000 | 4200 | 5200 | 08-01-2007
5000 | 4200 | 4800 | 08-08-2007
5000 | 4200 | 5200 | 08-15-2007
5000 | 4200 | 3500 | 12-10-2007
5000 | 4200 | 4500 | 01-01-2008
5000 | 4500 | 4200 | 01-01-2008
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING with null values
select x, y,
first_value(y) over w,
last_value(y) over w
from
(select x, x as y from generate_series(1,5) as x
union all select null, 42
union all select null, 43) ss
window w as
(order by x asc nulls first range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
| 42 | 42 | 43
| 43 | 42 | 43
1 | 1 | 1 | 3
2 | 2 | 1 | 4
3 | 3 | 1 | 5
4 | 4 | 2 | 5
5 | 5 | 3 | 5
(7 rows)
select x, y,
first_value(y) over w,
last_value(y) over w
from
(select x, x as y from generate_series(1,5) as x
union all select null, 42
union all select null, 43) ss
window w as
(order by x asc nulls last range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
1 | 1 | 1 | 3
2 | 2 | 1 | 4
3 | 3 | 1 | 5
4 | 4 | 2 | 5
5 | 5 | 3 | 5
| 42 | 42 | 43
| 43 | 42 | 43
(7 rows)
select x, y,
first_value(y) over w,
last_value(y) over w
from
(select x, x as y from generate_series(1,5) as x
union all select null, 42
union all select null, 43) ss
window w as
(order by x desc nulls first range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
| 43 | 43 | 42
| 42 | 43 | 42
5 | 5 | 5 | 3
4 | 4 | 5 | 2
3 | 3 | 5 | 1
2 | 2 | 4 | 1
1 | 1 | 3 | 1
(7 rows)
select x, y,
first_value(y) over w,
last_value(y) over w
from
(select x, x as y from generate_series(1,5) as x
union all select null, 42
union all select null, 43) ss
window w as
(order by x desc nulls last range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
5 | 5 | 5 | 3
4 | 4 | 5 | 2
3 | 3 | 5 | 1
2 | 2 | 4 | 1
1 | 1 | 3 | 1
| 42 | 42 | 43
| 43 | 42 | 43
(7 rows)
-- Check overflow behavior for various integer sizes
select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following)
from generate_series(32764, 32766) x;
x | last_value
-------+------------
32764 | 32766
32765 | 32766
32766 | 32766
(3 rows)
select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following)
from generate_series(-32766, -32764) x;
x | last_value
--------+------------
-32764 | -32766
-32765 | -32766
-32766 | -32766
(3 rows)
select x, last_value(x) over (order by x range between current row and 4 following)
from generate_series(2147483644, 2147483646) x;
x | last_value
------------+------------
2147483644 | 2147483646
2147483645 | 2147483646
2147483646 | 2147483646
(3 rows)
select x, last_value(x) over (order by x desc range between current row and 5 following)
from generate_series(-2147483646, -2147483644) x;
x | last_value
-------------+-------------
-2147483644 | -2147483646
-2147483645 | -2147483646
-2147483646 | -2147483646
(3 rows)
select x, last_value(x) over (order by x range between current row and 4 following)
from generate_series(9223372036854775804, 9223372036854775806) x;
x | last_value
---------------------+---------------------
9223372036854775804 | 9223372036854775806
9223372036854775805 | 9223372036854775806
9223372036854775806 | 9223372036854775806
(3 rows)
select x, last_value(x) over (order by x desc range between current row and 5 following)
from generate_series(-9223372036854775806, -9223372036854775804) x;
x | last_value
----------------------+----------------------
-9223372036854775804 | -9223372036854775806
-9223372036854775805 | -9223372036854775806
-9223372036854775806 | -9223372036854775806
(3 rows)
-- Test in_range for other numeric datatypes
create temp table numerics(
id int,
f_float4 float4,
f_float8 float8,
f_numeric numeric
);
insert into numerics values
(0, '-infinity', '-infinity', '-1000'), -- numeric type lacks infinities
(1, -3, -3, -3),
(2, -1, -1, -1),
(3, 0, 0, 0),
(4, 1.1, 1.1, 1.1),
(5, 1.12, 1.12, 1.12),
(6, 2, 2, 2),
(7, 100, 100, 100),
(8, 'infinity', 'infinity', '1000'),
(9, 'NaN', 'NaN', 'NaN');
select id, f_float4, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float4 range between
1 preceding and 1 following);
id | f_float4 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 3
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float4, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float4 range between
1 preceding and 1.1::float4 following);
id | f_float4 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 4
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float4, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float4 range between
'inf' preceding and 'inf' following);
id | f_float4 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 8
1 | -3 | 0 | 8
2 | -1 | 0 | 8
3 | 0 | 0 | 8
4 | 1.1 | 0 | 8
5 | 1.12 | 0 | 8
6 | 2 | 0 | 8
7 | 100 | 0 | 8
8 | Infinity | 0 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float4, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float4 range between
1.1 preceding and 'NaN' following); -- error, NaN disallowed
ERROR: invalid preceding or following size in window function
select id, f_float8, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float8 range between
1 preceding and 1 following);
id | f_float8 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 3
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float8, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float8 range between
1 preceding and 1.1::float8 following);
id | f_float8 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 4
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float8, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float8 range between
'inf' preceding and 'inf' following);
id | f_float8 | first_value | last_value
----+-----------+-------------+------------
0 | -Infinity | 0 | 8
1 | -3 | 0 | 8
2 | -1 | 0 | 8
3 | 0 | 0 | 8
4 | 1.1 | 0 | 8
5 | 1.12 | 0 | 8
6 | 2 | 0 | 8
7 | 100 | 0 | 8
8 | Infinity | 0 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_float8, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_float8 range between
1.1 preceding and 'NaN' following); -- error, NaN disallowed
ERROR: invalid preceding or following size in window function
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_numeric range between
1 preceding and 1 following);
id | f_numeric | first_value | last_value
----+-----------+-------------+------------
0 | -1000 | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 3
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | 1000 | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_numeric range between
1 preceding and 1.1::numeric following);
id | f_numeric | first_value | last_value
----+-----------+-------------+------------
0 | -1000 | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 4
4 | 1.1 | 4 | 6
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
8 | 1000 | 8 | 8
9 | NaN | 9 | 9
(10 rows)
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_numeric range between
1 preceding and 1.1::float8 following); -- currently unsupported
ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type numeric and offset type double precision
LINE 4: 1 preceding and 1.1::float8 following);
^
HINT: Cast the offset value to an appropriate type.
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_numeric range between
1.1 preceding and 'NaN' following); -- error, NaN disallowed
ERROR: invalid preceding or following size in window function
-- Test in_range for other datetime datatypes
create temp table datetimes(
id int,
f_time time,
f_timetz timetz,
f_interval interval,
f_timestamptz timestamptz,
f_timestamp timestamp
);
insert into datetimes values
(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'),
(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'),
(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'),
(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'),
(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'),
(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'),
(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'),
(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54');
select id, f_time, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_time range between
'70 min'::interval preceding and '2 hours'::interval following);
id | f_time | first_value | last_value
----+----------+-------------+------------
1 | 11:00:00 | 1 | 3
2 | 12:00:00 | 1 | 4
3 | 13:00:00 | 2 | 6
4 | 14:00:00 | 3 | 6
5 | 15:00:00 | 4 | 7
6 | 15:00:00 | 4 | 7
7 | 17:00:00 | 7 | 9
8 | 18:00:00 | 7 | 10
9 | 19:00:00 | 8 | 10
10 | 20:00:00 | 9 | 10
(10 rows)
select id, f_time, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_time desc range between
'70 min' preceding and '2 hours' following);
id | f_time | first_value | last_value
----+----------+-------------+------------
10 | 20:00:00 | 10 | 8
9 | 19:00:00 | 10 | 7
8 | 18:00:00 | 9 | 7
7 | 17:00:00 | 8 | 5
6 | 15:00:00 | 6 | 3
5 | 15:00:00 | 6 | 3
4 | 14:00:00 | 6 | 2
3 | 13:00:00 | 4 | 1
2 | 12:00:00 | 3 | 1
1 | 11:00:00 | 2 | 1
(10 rows)
select id, f_timetz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timetz range between
'70 min'::interval preceding and '2 hours'::interval following);
id | f_timetz | first_value | last_value
----+-------------+-------------+------------
1 | 11:00:00+01 | 1 | 3
2 | 12:00:00+01 | 1 | 4
3 | 13:00:00+01 | 2 | 6
4 | 14:00:00+01 | 3 | 6
5 | 15:00:00+01 | 4 | 7
6 | 15:00:00+01 | 4 | 7
7 | 17:00:00+01 | 7 | 9
8 | 18:00:00+01 | 7 | 10
9 | 19:00:00+01 | 8 | 10
10 | 20:00:00+01 | 9 | 10
(10 rows)
select id, f_timetz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timetz desc range between
'70 min' preceding and '2 hours' following);
id | f_timetz | first_value | last_value
----+-------------+-------------+------------
10 | 20:00:00+01 | 10 | 8
9 | 19:00:00+01 | 10 | 7
8 | 18:00:00+01 | 9 | 7
7 | 17:00:00+01 | 8 | 5
6 | 15:00:00+01 | 6 | 3
5 | 15:00:00+01 | 6 | 3
4 | 14:00:00+01 | 6 | 2
3 | 13:00:00+01 | 4 | 1
2 | 12:00:00+01 | 3 | 1
1 | 11:00:00+01 | 2 | 1
(10 rows)
select id, f_interval, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_interval range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_interval | first_value | last_value
----+------------+-------------+------------
1 | @ 1 year | 1 | 2
2 | @ 2 years | 1 | 3
3 | @ 3 years | 2 | 4
4 | @ 4 years | 3 | 6
5 | @ 5 years | 4 | 6
6 | @ 5 years | 4 | 6
7 | @ 7 years | 7 | 8
8 | @ 8 years | 7 | 9
9 | @ 9 years | 8 | 10
10 | @ 10 years | 9 | 10
(10 rows)
select id, f_interval, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_interval desc range between
'1 year' preceding and '1 year' following);
id | f_interval | first_value | last_value
----+------------+-------------+------------
10 | @ 10 years | 10 | 9
9 | @ 9 years | 10 | 8
8 | @ 8 years | 9 | 7
7 | @ 7 years | 8 | 7
6 | @ 5 years | 6 | 4
5 | @ 5 years | 6 | 4
4 | @ 4 years | 6 | 3
3 | @ 3 years | 4 | 2
2 | @ 2 years | 3 | 1
1 | @ 1 year | 2 | 1
(10 rows)
select id, f_timestamptz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamptz range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_timestamptz | first_value | last_value
----+------------------------------+-------------+------------
1 | Thu Oct 19 02:23:54 2000 PDT | 1 | 3
2 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4
3 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4
4 | Sat Oct 19 02:23:54 2002 PDT | 2 | 5
5 | Sun Oct 19 02:23:54 2003 PDT | 4 | 6
6 | Tue Oct 19 02:23:54 2004 PDT | 5 | 7
7 | Wed Oct 19 02:23:54 2005 PDT | 6 | 8
8 | Thu Oct 19 02:23:54 2006 PDT | 7 | 9
9 | Fri Oct 19 02:23:54 2007 PDT | 8 | 10
10 | Sun Oct 19 02:23:54 2008 PDT | 9 | 10
(10 rows)
select id, f_timestamptz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamptz desc range between
'1 year' preceding and '1 year' following);
id | f_timestamptz | first_value | last_value
----+------------------------------+-------------+------------
10 | Sun Oct 19 02:23:54 2008 PDT | 10 | 9
9 | Fri Oct 19 02:23:54 2007 PDT | 10 | 8
8 | Thu Oct 19 02:23:54 2006 PDT | 9 | 7
7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6
6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5
5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4
4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2
3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1
(10 rows)
select id, f_timestamp, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamp range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_timestamp | first_value | last_value
----+--------------------------+-------------+------------
1 | Thu Oct 19 10:23:54 2000 | 1 | 3
2 | Fri Oct 19 10:23:54 2001 | 1 | 4
3 | Fri Oct 19 10:23:54 2001 | 1 | 4
4 | Sat Oct 19 10:23:54 2002 | 2 | 5
5 | Sun Oct 19 10:23:54 2003 | 4 | 6
6 | Tue Oct 19 10:23:54 2004 | 5 | 7
7 | Wed Oct 19 10:23:54 2005 | 6 | 8
8 | Thu Oct 19 10:23:54 2006 | 7 | 9
9 | Fri Oct 19 10:23:54 2007 | 8 | 10
10 | Sun Oct 19 10:23:54 2008 | 9 | 10
(10 rows)
select id, f_timestamp, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamp desc range between
'1 year' preceding and '1 year' following);
id | f_timestamp | first_value | last_value
----+--------------------------+-------------+------------
10 | Sun Oct 19 10:23:54 2008 | 10 | 9
9 | Fri Oct 19 10:23:54 2007 | 10 | 8
8 | Thu Oct 19 10:23:54 2006 | 9 | 7
7 | Wed Oct 19 10:23:54 2005 | 8 | 6
6 | Tue Oct 19 10:23:54 2004 | 7 | 5
5 | Sun Oct 19 10:23:54 2003 | 6 | 4
4 | Sat Oct 19 10:23:54 2002 | 5 | 2
3 | Fri Oct 19 10:23:54 2001 | 4 | 1
2 | Fri Oct 19 10:23:54 2001 | 4 | 1
1 | Thu Oct 19 10:23:54 2000 | 3 | 1
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING error cases
select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
LINE 1: select sum(salary) over (order by enroll_date, salary range ...
^
select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
LINE 1: select sum(salary) over (range between '1 year'::interval pr...
^
select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type text
LINE 1: ... sum(salary) over (order by depname range between '1 year'::...
^
select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following
exclude ties), salary, enroll_date from empsalary;
ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type date and offset type integer
LINE 1: ...ll_date) over (order by enroll_date range between 1 precedin...
^
HINT: Cast the offset value to an appropriate type.
select max(enroll_date) over (order by salary range between -1 preceding and 2 following
exclude ties), salary, enroll_date from empsalary;
ERROR: invalid preceding or following size in window function
select max(enroll_date) over (order by salary range between 1 preceding and -2 following
exclude ties), salary, enroll_date from empsalary;
ERROR: invalid preceding or following size in window function
select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type integer and offset type interval
LINE 1: ...(enroll_date) over (order by salary range between '1 year'::...
^
HINT: Cast the offset value to an appropriate type.
select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
ERROR: invalid preceding or following size in window function
-- GROUPS tests
SELECT sum(unique1) over (order by four groups between unbounded preceding and current row),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
12 | 0 | 0
12 | 8 | 0
12 | 4 | 0
27 | 5 | 1
27 | 9 | 1
27 | 1 | 1
35 | 6 | 2
35 | 2 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
45 | 0 | 0
45 | 8 | 0
45 | 4 | 0
45 | 5 | 1
45 | 9 | 1
45 | 1 | 1
45 | 6 | 2
45 | 2 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between current row and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
45 | 0 | 0
45 | 8 | 0
45 | 4 | 0
33 | 5 | 1
33 | 9 | 1
33 | 1 | 1
18 | 6 | 2
18 | 2 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
45 | 0 | 0
45 | 8 | 0
45 | 4 | 0
45 | 5 | 1
45 | 9 | 1
45 | 1 | 1
33 | 6 | 2
33 | 2 | 2
18 | 3 | 3
18 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 1 following and unbounded following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
33 | 0 | 0
33 | 8 | 0
33 | 4 | 0
18 | 5 | 1
18 | 9 | 1
18 | 1 | 1
10 | 6 | 2
10 | 2 | 2
| 3 | 3
| 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
35 | 0 | 0
35 | 8 | 0
35 | 4 | 0
45 | 5 | 1
45 | 9 | 1
45 | 1 | 1
45 | 6 | 2
45 | 2 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
| 0 | 0
| 8 | 0
| 4 | 0
12 | 5 | 1
12 | 9 | 1
12 | 1 | 1
27 | 6 | 2
27 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
27 | 0 | 0
27 | 8 | 0
27 | 4 | 0
35 | 5 | 1
35 | 9 | 1
35 | 1 | 1
45 | 6 | 2
45 | 2 | 2
33 | 3 | 3
33 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following),
unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
12 | 0 | 0
12 | 8 | 0
12 | 4 | 0
15 | 5 | 1
15 | 9 | 1
15 | 1 | 1
8 | 6 | 2
8 | 2 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
exclude current row), unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
27 | 0 | 0
19 | 8 | 0
23 | 4 | 0
30 | 5 | 1
26 | 9 | 1
34 | 1 | 1
39 | 6 | 2
43 | 2 | 2
30 | 3 | 3
26 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
exclude group), unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
15 | 0 | 0
15 | 8 | 0
15 | 4 | 0
20 | 5 | 1
20 | 9 | 1
20 | 1 | 1
37 | 6 | 2
37 | 2 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
exclude ties), unique1, four
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
15 | 0 | 0
23 | 8 | 0
19 | 4 | 0
25 | 5 | 1
29 | 9 | 1
21 | 1 | 1
43 | 6 | 2
39 | 2 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following),unique1, four, ten
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four | ten
-----+---------+------+-----
0 | 0 | 0 | 0
1 | 1 | 1 | 1
2 | 2 | 2 | 2
3 | 3 | 3 | 3
4 | 4 | 0 | 4
5 | 5 | 1 | 5
6 | 6 | 2 | 6
7 | 7 | 3 | 7
8 | 8 | 0 | 8
9 | 9 | 1 | 9
(10 rows)
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four | ten
-----+---------+------+-----
| 0 | 0 | 0
| 1 | 1 | 1
| 2 | 2 | 2
| 3 | 3 | 3
| 4 | 0 | 4
| 5 | 1 | 5
| 6 | 2 | 6
| 7 | 3 | 7
| 8 | 0 | 8
| 9 | 1 | 9
(10 rows)
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four | ten
-----+---------+------+-----
| 0 | 0 | 0
| 1 | 1 | 1
| 2 | 2 | 2
| 3 | 3 | 3
| 4 | 0 | 4
| 5 | 1 | 5
| 6 | 2 | 6
| 7 | 3 | 7
| 8 | 0 | 8
| 9 | 1 | 9
(10 rows)
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four | ten
-----+---------+------+-----
0 | 0 | 0 | 0
1 | 1 | 1 | 1
2 | 2 | 2 | 2
3 | 3 | 3 | 3
4 | 4 | 0 | 4
5 | 5 | 1 | 5
6 | 6 | 2 | 6
7 | 7 | 3 | 7
8 | 8 | 0 | 8
9 | 9 | 1 | 9
(10 rows)
select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
lead(salary) over(order by enroll_date groups between 1 preceding and 1 following),
nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following),
salary, enroll_date from empsalary;
first_value | lead | nth_value | salary | enroll_date
-------------+------+-----------+--------+-------------
5000 | 6000 | 5000 | 5000 | 10-01-2006
5000 | 3900 | 5000 | 6000 | 10-01-2006
5000 | 4800 | 5000 | 3900 | 12-23-2006
3900 | 5200 | 3900 | 4800 | 08-01-2007
3900 | 4800 | 3900 | 5200 | 08-01-2007
4800 | 5200 | 4800 | 4800 | 08-08-2007
4800 | 3500 | 4800 | 5200 | 08-15-2007
5200 | 4500 | 5200 | 3500 | 12-10-2007
3500 | 4200 | 3500 | 4500 | 01-01-2008
3500 | | 3500 | 4200 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
lag(salary) over(order by enroll_date groups between 1 preceding and 1 following),
salary, enroll_date from empsalary;
last_value | lag | salary | enroll_date
------------+------+--------+-------------
3900 | | 5000 | 10-01-2006
3900 | 5000 | 6000 | 10-01-2006
5200 | 6000 | 3900 | 12-23-2006
4800 | 3900 | 4800 | 08-01-2007
4800 | 4800 | 5200 | 08-01-2007
5200 | 5200 | 4800 | 08-08-2007
3500 | 4800 | 5200 | 08-15-2007
4200 | 5200 | 3500 | 12-10-2007
4200 | 3500 | 4500 | 01-01-2008
4200 | 4500 | 4200 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date groups between 1 following and 3 following
exclude current row),
lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties),
nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following
exclude ties),
salary, enroll_date from empsalary;
first_value | lead | nth_value | salary | enroll_date
-------------+------+-----------+--------+-------------
3900 | 6000 | 3900 | 5000 | 10-01-2006
3900 | 3900 | 3900 | 6000 | 10-01-2006
4800 | 4800 | 4800 | 3900 | 12-23-2006
4800 | 5200 | 4800 | 4800 | 08-01-2007
4800 | 4800 | 4800 | 5200 | 08-01-2007
5200 | 5200 | 5200 | 4800 | 08-08-2007
3500 | 3500 | 3500 | 5200 | 08-15-2007
4500 | 4500 | 4500 | 3500 | 12-10-2007
| 4200 | | 4500 | 01-01-2008
| | | 4200 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 following and 3 following
exclude group),
lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group),
salary, enroll_date from empsalary;
last_value | lag | salary | enroll_date
------------+------+--------+-------------
4800 | | 5000 | 10-01-2006
4800 | 5000 | 6000 | 10-01-2006
5200 | 6000 | 3900 | 12-23-2006
3500 | 3900 | 4800 | 08-01-2007
3500 | 4800 | 5200 | 08-01-2007
4200 | 5200 | 4800 | 08-08-2007
4200 | 4800 | 5200 | 08-15-2007
4200 | 5200 | 3500 | 12-10-2007
| 3500 | 4500 | 01-01-2008
| 4500 | 4200 | 01-01-2008
(10 rows)
-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
WITH cte (x) AS (
SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
x | sum
----+-----
1 | 4
3 | 9
5 | 15
7 | 21
9 | 27
11 | 33
13 | 39
15 | 45
17 | 51
19 | 57
21 | 63
23 | 69
25 | 75
27 | 81
29 | 87
31 | 93
33 | 99
35 | 68
(18 rows)
WITH cte (x) AS (
SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following);
x | sum
----+-----
1 | 1
3 | 3
5 | 5
7 | 7
9 | 9
11 | 11
13 | 13
15 | 15
17 | 17
19 | 19
21 | 21
23 | 23
25 | 25
27 | 27
29 | 29
31 | 31
33 | 33
35 | 35
(18 rows)
WITH cte (x) AS (
SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following);
x | sum
----+-----
1 | 4
3 | 9
5 | 15
7 | 21
9 | 27
11 | 33
13 | 39
15 | 45
17 | 51
19 | 57
21 | 63
23 | 69
25 | 75
27 | 81
29 | 87
31 | 93
33 | 99
35 | 68
(18 rows)
WITH cte (x) AS (
select 1 union all select 1 union all select 1 union all
SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
x | sum
----+-----
1 | 2
1 | 3
1 | 7
5 | 13
7 | 21
9 | 27
11 | 33
13 | 39
15 | 45
17 | 51
19 | 57
21 | 63
23 | 69
25 | 75
27 | 81
29 | 87
31 | 93
33 | 99
35 | 105
37 | 111
39 | 117
41 | 123
43 | 129
45 | 135
47 | 141
49 | 96
(26 rows)
WITH cte (x) AS (
select 1 union all select 1 union all select 1 union all
SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following);
x | sum
----+-----
1 | 3
1 | 3
1 | 3
5 | 5
7 | 7
9 | 9
11 | 11
13 | 13
15 | 15
17 | 17
19 | 19
21 | 21
23 | 23
25 | 25
27 | 27
29 | 29
31 | 31
33 | 33
35 | 35
37 | 37
39 | 39
41 | 41
43 | 43
45 | 45
47 | 47
49 | 49
(26 rows)
WITH cte (x) AS (
select 1 union all select 1 union all select 1 union all
SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following);
x | sum
----+-----
1 | 8
1 | 8
1 | 8
5 | 15
7 | 21
9 | 27
11 | 33
13 | 39
15 | 45
17 | 51
19 | 57
21 | 63
23 | 69
25 | 75
27 | 81
29 | 87
31 | 93
33 | 99
35 | 105
37 | 111
39 | 117
41 | 123
43 | 129
45 | 135
47 | 141
49 | 96
(26 rows)
-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
count
-------
(0 rows)
-- check some degenerate cases
create temp table t1 (f1 int, f2 int8);
insert into t1 values (1,1),(1,2),(2,2);
select f1, sum(f1) over (partition by f1
range between 1 preceding and 1 following)
from t1 where f1 = f2; -- error, must have order by
ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
LINE 1: select f1, sum(f1) over (partition by f1
^
explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
---------------------------------
WindowAgg
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
(5 rows)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
f1 | sum
----+-----
1 | 1
2 | 2
(2 rows)
select f1, sum(f1) over (partition by f1, f1 order by f2
range between 2 preceding and 1 preceding)
from t1 where f1 = f2;
f1 | sum
----+-----
1 |
2 |
(2 rows)
select f1, sum(f1) over (partition by f1, f2 order by f2
range between 1 following and 2 following)
from t1 where f1 = f2;
f1 | sum
----+-----
1 |
2 |
(2 rows)
select f1, sum(f1) over (partition by f1
groups between 1 preceding and 1 following)
from t1 where f1 = f2; -- error, must have order by
ERROR: GROUPS mode requires an ORDER BY clause
LINE 1: select f1, sum(f1) over (partition by f1
^
explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
---------------------------------
WindowAgg
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
(5 rows)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
from t1 where f1 = f2;
f1 | sum
----+-----
1 | 1
2 | 2
(2 rows)
select f1, sum(f1) over (partition by f1, f1 order by f2
groups between 2 preceding and 1 preceding)
from t1 where f1 = f2;
f1 | sum
----+-----
1 |
2 |
(2 rows)
select f1, sum(f1) over (partition by f1, f2 order by f2
groups between 1 following and 2 following)
from t1 where f1 = f2;
f1 | sum
----+-----
1 |
2 |
(2 rows)
-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));
rank
------
1
(1 row)
-- can't order by another window function
SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random()));
ERROR: window functions are not allowed in window definitions
LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())...
^
-- some other errors
SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
ERROR: window functions are not allowed in WHERE
LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa...
^
SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
ERROR: window functions are not allowed in JOIN conditions
LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE...
^
SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
ERROR: window functions are not allowed in GROUP BY
LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
^
SELECT * FROM rank() OVER (ORDER BY random());
ERROR: syntax error at or near "ORDER"
LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
^
DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
ERROR: window functions are not allowed in WHERE
LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
^
DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
ERROR: window functions are not allowed in RETURNING
LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random...
^
SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
ERROR: window "w" is already defined
LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ...
^
SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
ERROR: syntax error at or near "ORDER"
LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
^
SELECT count() OVER () FROM tenk1;
ERROR: count(*) must be used to call a parameterless aggregate function
LINE 1: SELECT count() OVER () FROM tenk1;
^
SELECT generate_series(1, 100) OVER () FROM empsalary;
ERROR: OVER specified, but generate_series is not a window function nor an aggregate function
LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
^
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR: argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR: argument of nth_value must be greater than zero
-- filter
SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
depname
FROM empsalary GROUP BY depname;
sum | row_number | filtered_sum | depname
-------+------------+--------------+-----------
14600 | 3 | | sales
7400 | 2 | 3500 | personnel
25100 | 1 | 22600 | develop
(3 rows)
-- Test pushdown of quals into a subquery containing window functions
-- pushdown is safe because all PARTITION BY clauses include depname:
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT depname,
sum(salary) OVER (PARTITION BY depname) depsalary,
min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
QUERY PLAN
---------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
-> Sort
Sort Key: (((empsalary.depname)::text || 'A'::text))
-> WindowAgg
-> Seq Scan on empsalary
Filter: ((depname)::text = 'sales'::text)
(7 rows)
-- pushdown is unsafe because there's a PARTITION BY clause without depname:
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT depname,
sum(salary) OVER (PARTITION BY enroll_date) enroll_salary,
min(salary) OVER (PARTITION BY depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
QUERY PLAN
-----------------------------------------------------------
Subquery Scan on emp
Filter: ((emp.depname)::text = 'sales'::text)
-> WindowAgg
-> Sort
Sort Key: empsalary.depname
-> WindowAgg
-> Sort
Sort Key: empsalary.enroll_date
-> Seq Scan on empsalary
(9 rows)
-- cleanup
DROP TABLE empsalary;
-- test user-defined window function with named args and default args
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';
SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
nth_value_def | ten | four
---------------+-----+------
0 | 0 | 0
0 | 0 | 0
0 | 4 | 0
1 | 1 | 1
1 | 1 | 1
1 | 7 | 1
1 | 9 | 1
| 0 | 2
3 | 1 | 3
3 | 3 | 3
(10 rows)
SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
nth_value_def | ten | four
---------------+-----+------
0 | 0 | 0
0 | 0 | 0
0 | 4 | 0
1 | 1 | 1
1 | 1 | 1
1 | 7 | 1
1 | 9 | 1
0 | 0 | 2
1 | 1 | 3
1 | 3 | 3
(10 rows)
--
-- Test the basic moving-aggregate machinery
--
-- create aggregates that record the series of transform calls (these are
-- intentionally not true inverses)
CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE AGGREGATE logging_agg_nonstrict (anyelement)
(
stype = text,
sfunc = logging_sfunc_nonstrict,
mstype = text,
msfunc = logging_msfunc_nonstrict,
minvfunc = logging_minvfunc_nonstrict
);
CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement)
(
stype = text,
sfunc = logging_sfunc_nonstrict,
mstype = text,
msfunc = logging_msfunc_nonstrict,
minvfunc = logging_minvfunc_nonstrict,
initcond = 'I',
minitcond = 'MI'
);
CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '*' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '+' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE AGGREGATE logging_agg_strict (text)
(
stype = text,
sfunc = logging_sfunc_strict,
mstype = text,
msfunc = logging_msfunc_strict,
minvfunc = logging_minvfunc_strict
);
CREATE AGGREGATE logging_agg_strict_initcond (anyelement)
(
stype = text,
sfunc = logging_sfunc_strict,
mstype = text,
msfunc = logging_msfunc_strict,
minvfunc = logging_minvfunc_strict,
initcond = 'I',
minitcond = 'MI'
);
-- test strict and non-strict cases
SELECT
p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row,
logging_agg_nonstrict(v) over wnd as nstrict,
logging_agg_nonstrict_initcond(v) over wnd as nstrict_init,
logging_agg_strict(v::text) over wnd as strict,
logging_agg_strict_initcond(v) over wnd as strict_init
FROM (VALUES
(1, 1, NULL),
(1, 2, 'a'),
(1, 3, 'b'),
(1, 4, NULL),
(1, 5, NULL),
(1, 6, 'c'),
(2, 1, NULL),
(2, 2, 'x'),
(3, 1, 'z')
) AS t(p, i, v)
WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;
row | nstrict | nstrict_init | strict | strict_init
----------+-----------------------------------------------+-------------------------------------------------+-----------+----------------
1,1:NULL | +NULL | MI+NULL | | MI
1,2:a | +NULL+'a' | MI+NULL+'a' | a | MI+'a'
1,3:b | +NULL+'a'-NULL+'b' | MI+NULL+'a'-NULL+'b' | a+'b' | MI+'a'+'b'
1,4:NULL | +NULL+'a'-NULL+'b'-'a'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL | a+'b'-'a' | MI+'a'+'b'-'a'
1,5:NULL | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | | MI
1,6:c | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | c | MI+'c'
2,1:NULL | +NULL | MI+NULL | | MI
2,2:x | +NULL+'x' | MI+NULL+'x' | x | MI+'x'
3,1:z | +'z' | MI+'z' | z | MI+'z'
(9 rows)
-- and again, but with filter
SELECT
p::text || ',' || i::text || ':' ||
CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row,
logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt,
logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt,
logging_agg_strict(v::text) filter(where f) over wnd as strict_filt,
logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt
FROM (VALUES
(1, 1, true, NULL),
(1, 2, false, 'a'),
(1, 3, true, 'b'),
(1, 4, false, NULL),
(1, 5, false, NULL),
(1, 6, false, 'c'),
(2, 1, false, NULL),
(2, 2, true, 'x'),
(3, 1, true, 'z')
) AS t(p, i, f, v)
WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;
row | nstrict_filt | nstrict_init_filt | strict_filt | strict_init_filt
----------+--------------+-------------------+-------------+------------------
1,1:NULL | +NULL | MI+NULL | | MI
1,2:- | +NULL | MI+NULL | | MI
1,3:b | +'b' | MI+'b' | b | MI+'b'
1,4:- | +'b' | MI+'b' | b | MI+'b'
1,5:- | | MI | | MI
1,6:- | | MI | | MI
2,1:- | | MI | | MI
2,2:x | +'x' | MI+'x' | x | MI+'x'
3,1:z | +'z' | MI+'z' | z | MI+'z'
(9 rows)
-- test that volatile arguments disable moving-aggregate mode
SELECT
i::text || ':' || COALESCE(v::text, 'NULL') as row,
logging_agg_strict(v::text)
over wnd as inverse,
logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END)
over wnd as noinverse
FROM (VALUES
(1, 'a'),
(2, 'b'),
(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;
row | inverse | noinverse
-----+---------------+-----------
1:a | a | a
2:b | a+'b' | a*'b'
3:c | a+'b'-'a'+'c' | b*'c'
(3 rows)
SELECT
i::text || ':' || COALESCE(v::text, 'NULL') as row,
logging_agg_strict(v::text) filter(where true)
over wnd as inverse,
logging_agg_strict(v::text) filter(where random() >= 0)
over wnd as noinverse
FROM (VALUES
(1, 'a'),
(2, 'b'),
(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;
row | inverse | noinverse
-----+---------------+-----------
1:a | a | a
2:b | a+'b' | a*'b'
3:c | a+'b'-'a'+'c' | b*'c'
(3 rows)
-- test that non-overlapping windows don't use inverse transitions
SELECT
logging_agg_strict(v::text) OVER wnd
FROM (VALUES
(1, 'a'),
(2, 'b'),
(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
ORDER BY i;
logging_agg_strict
--------------------
a
b
c
(3 rows)
-- test that returning NULL from the inverse transition functions
-- restarts the aggregation from scratch. The second aggregate is supposed
-- to test cases where only some aggregates restart, the third one checks
-- that one aggregate restarting doesn't cause others to restart.
CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS
$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$
LANGUAGE SQL STRICT;
CREATE AGGREGATE sum_int_randomrestart (int4)
(
stype = int4,
sfunc = int4pl,
mstype = int4,
msfunc = int4pl,
minvfunc = sum_int_randrestart_minvfunc
);
WITH
vs AS (
SELECT i, (random() * 100)::int4 AS v
FROM generate_series(1, 100) AS i
),
sum_following AS (
SELECT i, SUM(v) OVER
(ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s
FROM vs
)
SELECT DISTINCT
sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1,
-sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2,
100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3
FROM vs
JOIN sum_following ON sum_following.i = vs.i
WINDOW fwd AS (
ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
eq1 | eq2 | eq3
-----+-----+-----
t | t | t
(1 row)
--
-- Test various built-in aggregates that have moving-aggregate support
--
-- test inverse transition functions handle NULLs properly
SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | avg
---+--------------------
1 | 1.5000000000000000
2 | 2.0000000000000000
3 |
4 |
(4 rows)
SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | avg
---+--------------------
1 | 1.5000000000000000
2 | 2.0000000000000000
3 |
4 |
(4 rows)
SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | avg
---+--------------------
1 | 1.5000000000000000
2 | 2.0000000000000000
3 |
4 |
(4 rows)
SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
i | avg
---+--------------------
1 | 2.0000000000000000
2 | 2.5000000000000000
3 |
4 |
(4 rows)
SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
i | avg
---+------------
1 | @ 1.5 secs
2 | @ 2 secs
3 |
4 |
(4 rows)
SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 3
2 | 2
3 |
4 |
(4 rows)
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 3
2 | 2
3 |
4 |
(4 rows)
SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 3
2 | 2
3 |
4 |
(4 rows)
SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-------
1 | $3.30
2 | $2.20
3 |
4 |
(4 rows)
SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+----------
1 | @ 3 secs
2 | @ 2 secs
3 |
4 |
(4 rows)
SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 3.3
2 | 2.2
3 |
4 |
(4 rows)
SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n);
sum
------
6.01
5
3
(3 rows)
SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | count
---+-------
1 | 2
2 | 1
3 | 0
4 | 0
(4 rows)
SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | count
---+-------
1 | 4
2 | 3
3 | 2
4 | 1
(4 rows)
SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_pop
-----------------------
21704.000000000000
13868.750000000000
11266.666666666667
4225.0000000000000000
0
(5 rows)
SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_pop
-----------------------
21704.000000000000
13868.750000000000
11266.666666666667
4225.0000000000000000
0
(5 rows)
SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_pop
-----------------------
21704.000000000000
13868.750000000000
11266.666666666667
4225.0000000000000000
0
(5 rows)
SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_pop
-----------------------
21704.000000000000
13868.750000000000
11266.666666666667
4225.0000000000000000
0
(5 rows)
SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_samp
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_samp
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_samp
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
var_samp
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
variance
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
variance
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
variance
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
variance
-----------------------
27130.000000000000
18491.666666666667
16900.000000000000
8450.0000000000000000
(5 rows)
SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_pop
---------------------
147.322774885623
147.322774885623
117.765657133139
106.144555520604
65.0000000000000000
0
(6 rows)
SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_pop
---------------------
147.322774885623
147.322774885623
117.765657133139
106.144555520604
65.0000000000000000
0
(6 rows)
SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_pop
---------------------
147.322774885623
147.322774885623
117.765657133139
106.144555520604
65.0000000000000000
0
(6 rows)
SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_pop
---------------------
147.322774885623
147.322774885623
117.765657133139
106.144555520604
65.0000000000000000
0
(6 rows)
SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_samp
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_samp
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_samp
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
stddev_samp
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
stddev
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
stddev
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
stddev
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
stddev
---------------------
164.711869639076
164.711869639076
135.984067694222
130.000000000000
91.9238815542511782
(6 rows)
-- test that inverse transition functions work with various frame options
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 1
2 | 2
3 |
4 |
(4 rows)
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
i | sum
---+-----
1 | 3
2 | 2
3 |
4 |
(4 rows)
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
i | sum
---+-----
1 | 3
2 | 6
3 | 9
4 | 7
(4 rows)
-- ensure aggregate over numeric properly recovers from NaN values
SELECT a, b,
SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);
a | b | sum
---+-----+-----
1 | 1 | 1
2 | 2 | 3
3 | NaN | NaN
4 | 3 | NaN
5 | 4 | 7
(5 rows)
-- It might be tempting for someone to add an inverse trans function for
-- float and double precision. This should not be done as it can give incorrect
-- results. This test should fail if anyone ever does this without thinking too
-- hard about it.
SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9')
FROM (VALUES(1,1e20),(2,1)) n(i,n);
to_char
--------------------------
100000000000000000000
1.0
(2 rows)
SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
i | b | bool_and | bool_or
---+---+----------+---------
1 | t | t | t
2 | t | f | t
3 | f | f | f
4 | f | f | t
5 | t | t | t
(5 rows)
Attachments:
[text/x-diff] 0001-Inlining-CTEs-v0002.patch (15.9K, ../20180725054236.GB17411@fetter.org/2-0001-Inlining-CTEs-v0002.patch)
download | inline diff:
From 2838de4fe03cce59c7e4c0d2dc6b09cba4cad11e Mon Sep 17 00:00:00 2001
From: David Fetter <david@fetter.org>
Date: Tue, 24 Jul 2018 22:09:55 -0700
Subject: [PATCH] Inlining CTEs v0002
To: pgsql-hackers@postgresql.org
---
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/plan/subselect.c | 152 ++++++++++++++++++++++
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/rowsecurity.out | 92 ++++++-------
src/test/regress/expected/rowtypes.out | 13 +-
src/test/regress/expected/rules.out | 6 +-
9 files changed, 201 insertions(+), 67 deletions(-)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.cindex 17b650b8cb..8ba2fc1aed 100644--- a/src/backend/nodes/copyfuncs.c+++ b/src/backend/nodes/copyfuncs.c@@ -2530,6 +2530,7 @@ _copyCommonTableExpr(const CommonTableExpr *from)
COPY_NODE_FIELD(ctequery);
COPY_LOCATION_FIELD(location);
COPY_SCALAR_FIELD(cterecursive);
+ COPY_SCALAR_FIELD(ctematerialized);
COPY_SCALAR_FIELD(cterefcount);
COPY_NODE_FIELD(ctecolnames);
COPY_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.cindex 378f2facb8..2a04506d0d 100644--- a/src/backend/nodes/equalfuncs.c+++ b/src/backend/nodes/equalfuncs.c@@ -2793,6 +2793,7 @@ _equalCommonTableExpr(const CommonTableExpr *a, const CommonTableExpr *b)
COMPARE_NODE_FIELD(ctequery);
COMPARE_LOCATION_FIELD(location);
COMPARE_SCALAR_FIELD(cterecursive);
+ COMPARE_SCALAR_FIELD(ctematerialized);
COMPARE_SCALAR_FIELD(cterefcount);
COMPARE_NODE_FIELD(ctecolnames);
COMPARE_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.cindex a6454ce28b..0ea6b7125a 100644--- a/src/backend/nodes/outfuncs.c+++ b/src/backend/nodes/outfuncs.c@@ -3080,6 +3080,7 @@ _outCommonTableExpr(StringInfo str, const CommonTableExpr *node)
WRITE_NODE_FIELD(ctequery);
WRITE_LOCATION_FIELD(location);
WRITE_BOOL_FIELD(cterecursive);
+ WRITE_BOOL_FIELD(ctematerialized);
WRITE_INT_FIELD(cterefcount);
WRITE_NODE_FIELD(ctecolnames);
WRITE_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.cindex 9a01eb6b63..01db3c1c85 100644--- a/src/backend/nodes/readfuncs.c+++ b/src/backend/nodes/readfuncs.c@@ -409,6 +409,7 @@ _readCommonTableExpr(void)
READ_NODE_FIELD(ctequery);
READ_LOCATION_FIELD(location);
READ_BOOL_FIELD(cterecursive);
+ READ_BOOL_FIELD(ctematerialized);
READ_INT_FIELD(cterefcount);
READ_NODE_FIELD(ctecolnames);
READ_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.cindex 83008d7661..e4d71f4652 100644--- a/src/backend/optimizer/plan/subselect.c+++ b/src/backend/optimizer/plan/subselect.c@@ -1146,6 +1146,141 @@ hash_ok_operator(OpExpr *expr)
}
+struct inline_cte_walker_ctx+{+ const char *ctename;+ int levelsup;+ int refcount;+ Query *ctequery;+ CommonTableExpr *cte;+};++static bool inline_cte_walker(Node *node, void *ctxp)+{+ struct inline_cte_walker_ctx *ctx = ctxp;++ if (!node)+ return false;++ if (IsA(node, Query))+ {+ /*+ * This one is a bit tricky. It's our job to handle the recursion here,+ * but we do some of the lifting normally handled by query_tree_walker+ * in order to get the sequence of operations right.+ *+ * First, if the Query we're looking at is the one containing our CTE+ * definition, then we don't need to recurse into our own CTE or CTEs+ * that are earlier in the list than ours (since cteList has been+ * sorted for us into dependency order). We could check whether a+ * nested query is hiding ours, but that seems too much of an edge case+ * to be worth optimizing (the levelsup check will ensure we don't+ * replace any CTEs improperly). So we scan the cteList ourselves+ * rather than having query_tree_walker do it.+ *+ * Second, we want to walk the rangetable _before_ replacing any+ * RTE_CTE nodes, in order to avoid re-walking the subquery we just+ * inlined. (range_table_walker, if told to visit the RTE nodes at all,+ * visits them before their content.) So we have range_table_walker+ * ignore the RTE nodes themselves and only walk their contents.+ *+ * Third, we scan the rangetable for RTE_CTE nodes to replace.+ *+ * Fourth, we use query_tree_walker to find and walk the rest of the+ * query, telling it to ignore the rangetable and CTEs.+ *+ * Note that ctx->levelsup is -1 on entry the first time, since we need+ * the incremented value to be 0 when scanning the content of the query+ * containing the definition.+ */+ Query *query = castNode(Query, node);+ ListCell *lc;+ bool do_replace = ctx->levelsup >= 0;++ ctx->levelsup++;++ foreach (lc, query->cteList)+ {+ CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc);++ if (!do_replace && strcmp(cte->ctename, ctx->ctename) == 0)+ do_replace = true;+ else if (do_replace)+ inline_cte_walker(cte->ctequery, ctxp);+ }++ range_table_walker(query->rtable, inline_cte_walker, ctxp, 0);++ foreach (lc, query->rtable)+ {+ RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc);++ if (rte->rtekind == RTE_CTE &&+ strcmp(rte->ctename, ctx->ctename) == 0 &&+ rte->ctelevelsup == ctx->levelsup)+ {+ Query *newquery = ctx->ctequery;++ /*+ * We need to do some work here that view rewrite does not, and+ * in turn we do not do some work that view rewrite does.+ *+ * Firstly, views can't have outer references but CTEs can+ * (especially in the case of CTEs referencing other CTEs), so+ * we need to fix up all levelsup attributes inside the CTE+ * query.+ *+ * Secondly, views (and explicit subqueries) currently have+ * different behaviour w.r.t. SELECT FOR UPDATE than CTEs do. A+ * FOR UPDATE clause is treated as extending into views and+ * subqueries, but not into CTEs. We preserve this distinction+ * by not trying to push rowmarks into the new subquery.+ *+ * We avoid copyObject if possible because subquery processing+ * copies the query too.+ */+ if (ctx->levelsup > 0)+ {+ newquery = copyObject(newquery);+ IncrementVarSublevelsUp((Node *) newquery, ctx->levelsup, 1);+ }++ /*+ * Here's where we do the actual substitution.+ */+ rte->rtekind = RTE_SUBQUERY;+ rte->subquery = newquery;+ rte->security_barrier = false;++ ctx->refcount--;+ }+ }++ query_tree_walker(query, inline_cte_walker, ctxp,+ QTW_IGNORE_RANGE_TABLE | QTW_IGNORE_CTE_SUBQUERIES);++ ctx->levelsup--;++ return false;+ }++ return expression_tree_walker(node, inline_cte_walker, ctxp);+}++static void inline_cte(PlannerInfo *root, CommonTableExpr *cte)+{+ struct inline_cte_walker_ctx ctx;+ ctx.ctequery = castNode(Query, cte->ctequery);+ ctx.ctename = cte->ctename;+ ctx.refcount = cte->cterefcount;+ ctx.levelsup = -1;+ ctx.cte = cte;+ inline_cte_walker((Node *) root->parse, &ctx);+ /* we must replace all references */+ Assert(ctx.refcount == 0);+}++
/*
* SS_process_ctes: process a query's WITH list
*
@@ -1183,6 +1318,23 @@ SS_process_ctes(PlannerInfo *root)
continue;
}
+ /*+ * Consider inlining the CTE rather than planning it separately.+ *+ * XXX this likely needs some additional checks.+ */+ if (cmdType == CMD_SELECT &&+ !cte->ctematerialized &&+ !cte->cterecursive &&+ (castNode(Query, cte->ctequery)->rowMarks == NIL))+ {+ inline_cte(root, cte);++ /* Make a dummy entry in cte_plan_ids */+ root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);+ continue;+ }+
/*
* Copy the source Query node. Probably not necessary, but let's keep
* this similar to make_subplan.
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex 7855cff30d..047d3de71c 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -1397,6 +1397,7 @@ typedef struct CommonTableExpr
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
+ bool ctematerialized; /* is this an optimization fence? */
int cterefcount; /* number of RTEs referencing this CTE
* (excluding internal self-references) */
List *ctecolnames; /* list of output column names */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex bc16ca4c43..0d8d382830 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -2181,29 +2181,25 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
- QUERY PLAN --------------------------------------------------+ QUERY PLAN +-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z1- Filter: (((a % 2) = 0) AND f_leak(b))- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
- -> Seq Scan on z2-(7 rows)+ -> Seq Scan on z1+ Filter: (((a % 2) = 0) AND f_leak(b))+(5 rows)
PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
QUERY PLAN
-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z2- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
-> Seq Scan on z1
Filter: (((a % 2) = 0) AND f_leak(b))
-(7 rows)+(5 rows)
SET ROLE regress_rls_group1;
SELECT * FROM z1 WHERE f_leak(b);
@@ -2230,28 +2226,24 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
(2 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
- QUERY PLAN --------------------------------------------------+ QUERY PLAN +-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z1- Filter: (((a % 2) = 0) AND f_leak(b))- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
- -> Seq Scan on z2-(7 rows)+ -> Seq Scan on z1+ Filter: (((a % 2) = 0) AND f_leak(b))+(5 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
QUERY PLAN
-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z2- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
-> Seq Scan on z1
Filter: (((a % 2) = 0) AND f_leak(b))
-(7 rows)+(5 rows)
SET SESSION AUTHORIZATION regress_rls_carol;
SELECT * FROM z1 WHERE f_leak(b);
@@ -2278,28 +2270,24 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
(2 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
- QUERY PLAN --------------------------------------------------+ QUERY PLAN +-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z1- Filter: (((a % 2) = 1) AND f_leak(b))- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
- -> Seq Scan on z2-(7 rows)+ -> Seq Scan on z1+ Filter: (((a % 2) = 1) AND f_leak(b))+(5 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
QUERY PLAN
-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z2- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
-> Seq Scan on z1
Filter: (((a % 2) = 1) AND f_leak(b))
-(7 rows)+(5 rows)
SET ROLE regress_rls_group2;
SELECT * FROM z1 WHERE f_leak(b);
@@ -2326,28 +2314,24 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
(2 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
- QUERY PLAN --------------------------------------------------+ QUERY PLAN +-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z1- Filter: (((a % 2) = 1) AND f_leak(b))- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
- -> Seq Scan on z2-(7 rows)+ -> Seq Scan on z1+ Filter: (((a % 2) = 1) AND f_leak(b))+(5 rows)
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
QUERY PLAN
-----------------------------------------------------
Nested Loop
- CTE q- -> Seq Scan on z2- -> CTE Scan on q+ -> Seq Scan on z2
-> Materialize
-> Seq Scan on z1
Filter: (((a % 2) = 1) AND f_leak(b))
-(7 rows)+(5 rows)
--
-- Views should follow policy for view owner.
@@ -2854,13 +2838,11 @@ NOTICE: f_leak => 98f13708210194c475687be6106a3b84
(11 rows)
EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
- QUERY PLAN --------------------------------------------------- CTE Scan on cte1- CTE cte1- -> Seq Scan on t1- Filter: (((a % 2) = 0) AND f_leak(b))-(4 rows)+ QUERY PLAN +-----------------------------------------+ Seq Scan on t1+ Filter: (((a % 2) = 0) AND f_leak(b))+(2 rows)
WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
ERROR: new row violates row-level security policy for table "t1"
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.outindex 30053d07df..c0df2f6333 100644--- a/src/test/regress/expected/rowtypes.out+++ b/src/test/regress/expected/rowtypes.out@@ -1041,14 +1041,11 @@ with r(a,b) as
(values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) )
select r, r is null as isnull, r is not null as isnotnull from r;
- QUERY PLAN ------------------------------------------------------------ CTE Scan on r- Output: r.*, (r.* IS NULL), (r.* IS NOT NULL)- CTE r- -> Values Scan on "*VALUES*"- Output: "*VALUES*".column1, "*VALUES*".column2-(5 rows)+ QUERY PLAN +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Values Scan on "*VALUES*"+ Output: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NOT DISTINCT FROM NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS DISTINCT FROM NULL))+(2 rows)
with r(a,b) as
(values (1,row(1,2)), (1,row(null,null)), (1,null),
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.outindex 744d501e31..882469b79f 100644--- a/src/test/regress/expected/rules.out+++ b/src/test/regress/expected/rules.out@@ -3135,10 +3135,8 @@ RETURNING *;
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: hat_data_unique_idx
Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
- CTE data- -> Values Scan on "*VALUES*"- -> CTE Scan on data-(7 rows)+ -> Values Scan on "*VALUES*"+(5 rows)
SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
hat_name | hat_color
--
2.17.1
[text/plain] regression.diffs (970B, ../20180725054236.GB17411@fetter.org/3-regression.diffs)
download | inline:
*** /home/shackle/pggit/postgresql/src/test/regress/expected/window.out 2018-07-24 22:14:09.015838790 -0700
--- /home/shackle/pggit/postgresql/src/test/regress/results/window.out 2018-07-24 22:20:36.766079578 -0700
***************
*** 3297,3303 ****
);
eq1 | eq2 | eq3
-----+-----+-----
! t | t | t
(1 row)
--
--- 3297,3303 ----
);
eq1 | eq2 | eq3
-----+-----+-----
! f | f | t
(1 row)
--
======================================================================
*** /home/shackle/pggit/postgresql/src/test/regress/expected/with.out 2018-07-24 22:14:09.017838781 -0700
--- /home/shackle/pggit/postgresql/src/test/regress/results/with.out 2018-07-24 22:20:37.562075966 -0700
***************
*** 18,24 ****
) ss;
count
-------
! 5
(1 row)
-- WITH RECURSIVE
--- 18,24 ----
) ss;
count
-------
! 10
(1 row)
-- WITH RECURSIVE
======================================================================
[text/plain] with.out (47.9K, ../20180725054236.GB17411@fetter.org/4-with.out)
download | inline:
--
-- Tests for common table expressions (WITH query, ... SELECT ...)
--
-- Basic WITH
WITH q1(x,y) AS (SELECT 1,2)
SELECT * FROM q1, q1 AS q2;
x | y | x | y
---+---+---+---
1 | 2 | 1 | 2
(1 row)
-- Multiple uses are evaluated only once
SELECT count(*) FROM (
WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
SELECT * FROM q1
UNION
SELECT * FROM q1
) ss;
count
-------
5
(1 row)
-- WITH RECURSIVE
-- sum of 1..100
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
sum
------
5050
(1 row)
WITH RECURSIVE t(n) AS (
SELECT (VALUES(1))
UNION ALL
SELECT n+1 FROM t WHERE n < 5
)
SELECT * FROM t;
n
---
1
2
3
4
5
(5 rows)
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums WHERE n < 5;
SELECT * FROM nums;
n
---
1
2
3
4
5
(5 rows)
CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums WHERE n < 6;
SELECT * FROM nums;
n
---
1
2
3
4
5
6
(6 rows)
-- This is an infinite loop with UNION ALL, but not with UNION
WITH RECURSIVE t(n) AS (
SELECT 1
UNION
SELECT 10-n FROM t)
SELECT * FROM t;
n
---
1
9
(2 rows)
-- This'd be an infinite loop, but outside query reads only as much as needed
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t)
SELECT * FROM t LIMIT 10;
n
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
-- UNION case should have same property
WITH RECURSIVE t(n) AS (
SELECT 1
UNION
SELECT n+1 FROM t)
SELECT * FROM t LIMIT 10;
n
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
-- Test behavior with an unknown-type literal in the WITH
WITH q AS (SELECT 'foo' AS x)
SELECT x, x IS OF (text) AS is_text FROM q;
x | is_text
-----+---------
foo | t
(1 row)
WITH RECURSIVE t(n) AS (
SELECT 'foo'
UNION ALL
SELECT n || ' bar' FROM t WHERE length(n) < 20
)
SELECT n, n IS OF (text) AS is_text FROM t;
n | is_text
-------------------------+---------
foo | t
foo bar | t
foo bar bar | t
foo bar bar bar | t
foo bar bar bar bar | t
foo bar bar bar bar bar | t
(6 rows)
-- In a perfect world, this would work and resolve the literal as int ...
-- but for now, we have to be content with resolving to text too soon.
WITH RECURSIVE t(n) AS (
SELECT '7'
UNION ALL
SELECT n+1 FROM t WHERE n < 10
)
SELECT n, n IS OF (int) AS is_int FROM t;
ERROR: operator does not exist: text + integer
LINE 4: SELECT n+1 FROM t WHERE n < 10
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
--
-- Some examples with a tree
--
-- department structure represented here is as follows:
--
-- ROOT-+->A-+->B-+->C
-- | |
-- | +->D-+->F
-- +->E-+->G
CREATE TEMP TABLE department (
id INTEGER PRIMARY KEY, -- department ID
parent_department INTEGER REFERENCES department, -- upper department ID
name TEXT -- department name
);
INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 5, 'G');
-- extract all departments under 'A'. Result should be A, B, C, D and F
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT name as root_name, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
root_name | id | parent_department | name
-----------+----+-------------------+------
A | 1 | 0 | A
A | 2 | 1 | B
A | 3 | 2 | C
A | 4 | 2 | D
A | 6 | 4 | F
(5 rows)
-- extract all departments under 'A' with "level" number
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
-- non recursive term
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
level | id | parent_department | name
-------+----+-------------------+------
1 | 1 | 0 | A
2 | 2 | 1 | B
3 | 3 | 2 | C
3 | 4 | 2 | D
4 | 6 | 4 | F
(5 rows)
-- extract all departments under 'A' with "level" number.
-- Only shows level 2 or more
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
-- non recursive term
SELECT 1, * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
level | id | parent_department | name
-------+----+-------------------+------
2 | 2 | 1 | B
3 | 3 | 2 | C
3 | 4 | 2 | D
4 | 6 | 4 | F
(4 rows)
-- "RECURSIVE" is ignored if the query has no self-reference
WITH RECURSIVE subdepartment AS
(
-- note lack of recursive UNION structure
SELECT * FROM department WHERE name = 'A'
)
SELECT * FROM subdepartment ORDER BY name;
id | parent_department | name
----+-------------------+------
1 | 0 | A
(1 row)
-- inside subqueries
SELECT count(*) FROM (
WITH RECURSIVE t(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
)
SELECT * FROM t) AS t WHERE n < (
SELECT count(*) FROM (
WITH RECURSIVE t(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
)
SELECT * FROM t WHERE n < 50000
) AS t WHERE n < 100);
count
-------
98
(1 row)
-- use same CTE twice at different subquery levels
WITH q1(x,y) AS (
SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
)
SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
count
-------
50
(1 row)
-- via a VIEW
CREATE TEMPORARY VIEW vsubdepartment AS
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment;
SELECT * FROM vsubdepartment ORDER BY name;
id | parent_department | name
----+-------------------+------
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
6 | 4 | F
(5 rows)
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
pg_get_viewdef
-----------------------------------------------
WITH RECURSIVE subdepartment AS ( +
SELECT department.id, +
department.parent_department, +
department.name +
FROM department +
WHERE (department.name = 'A'::text)+
UNION ALL +
SELECT d.id, +
d.parent_department, +
d.name +
FROM department d, +
subdepartment sd +
WHERE (d.parent_department = sd.id)+
) +
SELECT subdepartment.id, +
subdepartment.parent_department, +
subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
pg_get_viewdef
---------------------------------------------
WITH RECURSIVE subdepartment AS ( +
SELECT department.id, +
department.parent_department, +
department.name +
FROM department +
WHERE department.name = 'A'::text+
UNION ALL +
SELECT d.id, +
d.parent_department, +
d.name +
FROM department d, +
subdepartment sd +
WHERE d.parent_department = sd.id+
) +
SELECT subdepartment.id, +
subdepartment.parent_department, +
subdepartment.name +
FROM subdepartment;
(1 row)
-- Another reverse-listing example
CREATE VIEW sums_1_100 AS
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
\d+ sums_1_100
View "public.sums_1_100"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
sum | bigint | | | | plain |
View definition:
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT t_1.n + 1
FROM t t_1
WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
-- corner case in which sub-WITH gets initialized first
with recursive q as (
select * from department
union all
(with x as (select * from q)
select * from x)
)
select * from q limit 24;
id | parent_department | name
----+-------------------+------
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
(24 rows)
with recursive q as (
select * from department
union all
(with recursive x as (
select * from department
union all
(select * from q union all select * from x)
)
select * from x)
)
select * from q limit 32;
id | parent_department | name
----+-------------------+------
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
0 | | ROOT
1 | 0 | A
2 | 1 | B
3 | 2 | C
4 | 2 | D
5 | 0 | E
6 | 4 | F
7 | 5 | G
(32 rows)
-- recursive term has sub-UNION
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j+1 FROM
(SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
JOIN t ON (t2.i = t.i+1))
SELECT * FROM t;
i | j
---+---
1 | 2
2 | 3
3 | 4
(3 rows)
--
-- different tree example
--
CREATE TEMPORARY TABLE tree(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES tree(id)
);
INSERT INTO tree
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
--
-- get all paths from "second level" nodes to leaf nodes
--
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
(t1.path[1] = t2.path[1] AND
array_upper(t1.path,1) = 1 AND
array_upper(t2.path,1) > 1)
ORDER BY t1.id, t2.id;
id | path | id | path
----+------+----+-------------
2 | {2} | 4 | {2,4}
2 | {2} | 5 | {2,5}
2 | {2} | 6 | {2,6}
2 | {2} | 9 | {2,4,9}
2 | {2} | 10 | {2,4,10}
2 | {2} | 14 | {2,4,9,14}
3 | {3} | 7 | {3,7}
3 | {3} | 8 | {3,8}
3 | {3} | 11 | {3,7,11}
3 | {3} | 12 | {3,7,12}
3 | {3} | 13 | {3,7,13}
3 | {3} | 15 | {3,7,11,15}
3 | {3} | 16 | {3,7,11,16}
(13 rows)
-- just count 'em
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
(t1.path[1] = t2.path[1] AND
array_upper(t1.path,1) = 1 AND
array_upper(t2.path,1) > 1)
GROUP BY t1.id
ORDER BY t1.id;
id | count
----+-------
2 | 6
3 | 7
(2 rows)
-- this variant tickled a whole-row-variable bug in 8.4devel
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[]::integer[])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
(t1.id=t2.id);
id | path | t2
----+-------------+--------------------
1 | {} | (1,{})
2 | {2} | (2,{2})
3 | {3} | (3,{3})
4 | {2,4} | (4,"{2,4}")
5 | {2,5} | (5,"{2,5}")
6 | {2,6} | (6,"{2,6}")
7 | {3,7} | (7,"{3,7}")
8 | {3,8} | (8,"{3,8}")
9 | {2,4,9} | (9,"{2,4,9}")
10 | {2,4,10} | (10,"{2,4,10}")
11 | {3,7,11} | (11,"{3,7,11}")
12 | {3,7,12} | (12,"{3,7,12}")
13 | {3,7,13} | (13,"{3,7,13}")
14 | {2,4,9,14} | (14,"{2,4,9,14}")
15 | {3,7,11,15} | (15,"{3,7,11,15}")
16 | {3,7,11,16} | (16,"{3,7,11,16}")
(16 rows)
--
-- test cycle detection
--
create temp table graph( f int, t int, label text );
insert into graph values
(1, 2, 'arc 1 -> 2'),
(1, 3, 'arc 1 -> 3'),
(2, 3, 'arc 2 -> 3'),
(1, 4, 'arc 1 -> 4'),
(4, 5, 'arc 4 -> 5'),
(5, 1, 'arc 5 -> 1');
with recursive search_graph(f, t, label, path, cycle) as (
select *, array[row(g.f, g.t)], false from graph g
union all
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
from graph g, search_graph sg
where g.f = sg.t and not cycle
)
select * from search_graph;
f | t | label | path | cycle
---+---+------------+-------------------------------------------+-------
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
(25 rows)
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, path, cycle) as (
select *, array[row(g.f, g.t)], false from graph g
union all
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
from graph g, search_graph sg
where g.f = sg.t and not cycle
)
select * from search_graph order by path;
f | t | label | path | cycle
---+---+------------+-------------------------------------------+-------
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
(25 rows)
--
-- test multiple WITH queries
--
WITH RECURSIVE
y (id) AS (VALUES (1)),
x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
SELECT * FROM x;
id
----
1
2
3
4
5
(5 rows)
-- forward reference OK
WITH RECURSIVE
x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS (values (1))
SELECT * FROM x;
id
----
1
2
3
4
5
(5 rows)
WITH RECURSIVE
x(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
id | id
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 |
7 |
8 |
9 |
10 |
(10 rows)
WITH RECURSIVE
x(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
id | id
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 |
(6 rows)
WITH RECURSIVE
x(id) AS
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
y(id) AS
(SELECT * FROM x UNION ALL SELECT * FROM x),
z(id) AS
(SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
SELECT * FROM z;
id
----
1
2
3
2
3
4
3
4
5
4
5
6
5
6
7
6
7
8
7
8
9
8
9
10
9
10
10
(27 rows)
WITH RECURSIVE
x(id) AS
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
y(id) AS
(SELECT * FROM x UNION ALL SELECT * FROM x),
z(id) AS
(SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
SELECT * FROM z;
id
----
1
2
3
1
2
3
2
3
4
2
3
4
3
4
5
3
4
5
4
5
6
4
5
6
5
6
7
5
6
7
6
7
8
6
7
8
7
8
9
7
8
9
8
9
10
8
9
10
9
10
9
10
10
10
(54 rows)
--
-- Test WITH attached to a data-modifying statement
--
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
WITH t AS (
SELECT a FROM y
)
INSERT INTO y
SELECT a+20 FROM t RETURNING *;
a
----
21
22
23
24
25
26
27
28
29
30
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
24
25
26
27
28
29
30
(20 rows)
WITH t AS (
SELECT a FROM y
)
UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)
WITH RECURSIVE t(a) AS (
SELECT 11
UNION ALL
SELECT a+1 FROM t WHERE a < 50
)
DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
DROP TABLE y;
--
-- error cases
--
-- INTERSECT
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
^
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
^
-- EXCEPT
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
^
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
^
-- no non-recursive term
WITH RECURSIVE x(n) AS (SELECT n FROM x)
SELECT * FROM x;
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
^
-- recursive term in the left hand side (strictly speaking, should allow this)
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within its non-recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
^
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
^
-- RIGHT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
^
-- FULL JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
UNION ALL
SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within an outer join
LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
^
-- subquery
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
WHERE n IN (SELECT * FROM x))
SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within a subquery
LINE 2: WHERE n IN (SELECT * FROM x))
^
-- aggregate functions
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
SELECT * FROM x;
ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
^
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
SELECT * FROM x;
ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
^
-- ORDER BY
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
SELECT * FROM x;
ERROR: ORDER BY in a recursive query is not implemented
LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
^
-- LIMIT/OFFSET
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
SELECT * FROM x;
ERROR: OFFSET in a recursive query is not implemented
LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
^
-- FOR UPDATE
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
SELECT * FROM x;
ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
-- target list has a recursive query name
WITH RECURSIVE x(id) AS (values (1)
UNION ALL
SELECT (SELECT * FROM x) FROM x WHERE id < 5
) SELECT * FROM x;
ERROR: recursive reference to query "x" must not appear within a subquery
LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
^
-- mutual recursive query (not implemented)
WITH RECURSIVE
x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
SELECT * FROM x;
ERROR: mutual recursion between WITH items is not implemented
LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
^
-- non-linear recursion is not allowed
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
UNION ALL
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
SELECT * FROM
(SELECT i+1 FROM foo WHERE i < 10
UNION ALL
SELECT i+1 FROM foo WHERE i < 5) AS t
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
EXCEPT
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear within EXCEPT
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
WITH RECURSIVE foo(i) AS
(values (1)
UNION ALL
(SELECT i+1 FROM foo WHERE i < 10
INTERSECT
SELECT i+1 FROM foo WHERE i < 5)
) SELECT * FROM foo;
ERROR: recursive reference to query "foo" must not appear more than once
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
^
-- Wrong type induced from non-recursive term
WITH RECURSIVE foo(i) AS
(SELECT i FROM (VALUES(1),(2)) t(i)
UNION ALL
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
SELECT * FROM foo;
ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
^
HINT: Cast the output of the non-recursive term to the correct type.
-- rejects different typmod, too (should we allow this?)
WITH RECURSIVE foo(i) AS
(SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
UNION ALL
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
SELECT * FROM foo;
ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
^
HINT: Cast the output of the non-recursive term to the correct type.
-- disallow OLD/NEW reference in CTE
CREATE TEMPORARY TABLE x (n integer);
CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
ERROR: cannot refer to OLD within WITH query
--
-- test for bug #4902
--
with cte(foo) as ( values(42) ) values((select foo from cte));
column1
---------
42
(1 row)
with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
foo
-----
42
(1 row)
-- test CTE referencing an outer-level variable (to see that changed-parameter
-- signaling still works properly after fixing this bug)
select ( with cte(foo) as ( values(f1) )
select (select foo from cte) )
from int4_tbl;
foo
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
select ( with cte(foo) as ( values(f1) )
values((select foo from cte)) )
from int4_tbl;
column1
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
--
-- test for nested-recursive-WITH bug
--
WITH RECURSIVE t(j) AS (
WITH RECURSIVE s(i) AS (
VALUES (1)
UNION ALL
SELECT i+1 FROM s WHERE i < 10
)
SELECT i FROM s
UNION ALL
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
j
----
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
3
4
5
6
7
8
9
10
4
5
6
7
8
9
10
5
6
7
8
9
10
6
7
8
9
10
7
8
9
10
8
9
10
9
10
10
(55 rows)
--
-- test WITH attached to intermediate-level set operation
--
WITH outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3)
)
SELECT * FROM outermost ORDER BY 1;
x
---
1
2
3
(3 rows)
WITH outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM outermost -- fail
UNION SELECT * FROM innermost)
)
SELECT * FROM outermost ORDER BY 1;
ERROR: relation "outermost" does not exist
LINE 4: SELECT * FROM outermost
^
DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
WITH RECURSIVE outermost(x) AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM outermost
UNION SELECT * FROM innermost)
)
SELECT * FROM outermost ORDER BY 1;
x
---
1
2
(2 rows)
WITH RECURSIVE outermost(x) AS (
WITH innermost as (SELECT 2 FROM outermost) -- fail
SELECT * FROM innermost
UNION SELECT * from outermost
)
SELECT * FROM outermost ORDER BY 1;
ERROR: recursive reference to query "outermost" must not appear within a subquery
LINE 2: WITH innermost as (SELECT 2 FROM outermost)
^
--
-- This test will fail with the old implementation of PARAM_EXEC parameter
-- assignment, because the "q1" Var passed down to A's targetlist subselect
-- looks exactly like the "A.id" Var passed down to C's subselect, causing
-- the old code to give them the same runtime PARAM_EXEC slot. But the
-- lifespans of the two parameters overlap, thanks to B also reading A.
--
with
A as ( select q2 as id, (select q1) as x from int8_tbl ),
B as ( select id, row_number() over (partition by id) as r from A ),
C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
select * from C;
id | array
-------------------+-------------------------------------
456 | {456}
4567890123456789 | {4567890123456789,4567890123456789}
123 | {123}
4567890123456789 | {4567890123456789,4567890123456789}
-4567890123456789 | {-4567890123456789}
(5 rows)
--
-- Test CTEs read in non-initialization orders
--
WITH RECURSIVE
tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION ALL (
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
WHERE e.row_type = 'false'
)
SELECT * FROM first_remaining
UNION ALL SELECT * FROM effect
)
)
SELECT * FROM iter;
id_key | row_type | link
--------+----------+------
0 | base | 17
1 | true | 17
2 | true | 17
3 | true | 17
4 | true | 17
5 | true | 17
6 | true | 17
(7 rows)
WITH RECURSIVE
tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION (
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
WHERE e.row_type = 'false'
)
SELECT * FROM first_remaining
UNION ALL SELECT * FROM effect
)
)
SELECT * FROM iter;
id_key | row_type | link
--------+----------+------
0 | base | 17
1 | true | 17
2 | true | 17
3 | true | 17
4 | true | 17
5 | true | 17
6 | true | 17
(7 rows)
--
-- Data-modifying statements in WITH
--
-- INSERT ... RETURNING
WITH t AS (
INSERT INTO y
VALUES
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20)
RETURNING *
)
SELECT * FROM t;
a
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)
-- UPDATE ... RETURNING
WITH t AS (
UPDATE y
SET a=a+1
RETURNING *
)
SELECT * FROM t;
a
----
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(20 rows)
SELECT * FROM y;
a
----
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(20 rows)
-- DELETE ... RETURNING
WITH t AS (
DELETE FROM y
WHERE a <= 10
RETURNING *
)
SELECT * FROM t;
a
----
2
3
4
5
6
7
8
9
10
(9 rows)
SELECT * FROM y;
a
----
11
12
13
14
15
16
17
18
19
20
21
(11 rows)
-- forward reference
WITH RECURSIVE t AS (
INSERT INTO y
SELECT a+5 FROM t2 WHERE a > 5
RETURNING *
), t2 AS (
UPDATE y SET a=a-11 RETURNING *
)
SELECT * FROM t
UNION ALL
SELECT * FROM t2;
a
----
11
12
13
14
15
0
1
2
3
4
5
6
7
8
9
10
(16 rows)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
(16 rows)
-- unconditional DO INSTEAD rule
CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
INSERT INTO y VALUES(42) RETURNING *;
WITH t AS (
DELETE FROM y RETURNING *
)
SELECT * FROM t;
a
----
42
(1 row)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
42
(17 rows)
DROP RULE y_rule ON y;
-- check merging of outer CTE with CTE in a rule action
CREATE TEMP TABLE bug6051 AS
select i from generate_series(1,3) as t(i);
SELECT * FROM bug6051;
i
---
1
2
3
(3 rows)
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
i
---
1
2
3
(3 rows)
CREATE TEMP TABLE bug6051_2 (i int);
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
INSERT INTO bug6051_2
SELECT NEW.i;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
i
---
(0 rows)
SELECT * FROM bug6051_2;
i
---
1
2
3
(3 rows)
-- a truly recursive CTE in the same list
WITH RECURSIVE t(a) AS (
SELECT 0
UNION ALL
SELECT a+1 FROM t WHERE a+1 < 5
), t2 as (
INSERT INTO y
SELECT * FROM t RETURNING *
)
SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
a
---
0
1
2
3
4
(5 rows)
SELECT * FROM y;
a
----
0
1
2
3
4
5
6
11
7
12
8
13
9
14
10
15
42
0
1
2
3
4
(22 rows)
-- data-modifying WITH in a modifying statement
WITH t AS (
DELETE FROM y
WHERE a <= 10
RETURNING *
)
INSERT INTO y SELECT -a FROM t RETURNING *;
a
-----
0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-10
0
-1
-2
-3
-4
(16 rows)
SELECT * FROM y;
a
-----
11
12
13
14
15
42
0
-1
-2
-3
-4
-5
-6
-7
-8
-9
-10
0
-1
-2
-3
-4
(22 rows)
-- check that WITH query is run to completion even if outer query isn't
WITH t AS (
UPDATE y SET a = a * 100 RETURNING *
)
SELECT * FROM t LIMIT 10;
a
------
1100
1200
1300
1400
1500
4200
0
-100
-200
-300
(10 rows)
SELECT * FROM y;
a
-------
1100
1200
1300
1400
1500
4200
0
-100
-200
-300
-400
-500
-600
-700
-800
-900
-1000
0
-100
-200
-300
-400
(22 rows)
-- data-modifying WITH containing INSERT...ON CONFLICT DO UPDATE
CREATE TABLE withz AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
ALTER TABLE withz ADD UNIQUE (k);
WITH t AS (
INSERT INTO withz SELECT i, 'insert'
FROM generate_series(0, 16) i
ON CONFLICT (k) DO UPDATE SET v = withz.v || ', now update'
RETURNING *
)
SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
k | v | a
---+--------+---
0 | insert | 0
0 | insert | 0
(2 rows)
-- Test EXCLUDED.* reference within CTE
WITH aa AS (
INSERT INTO withz VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v
WHERE withz.k != EXCLUDED.k
RETURNING *
)
SELECT * FROM aa;
k | v
---+---
(0 rows)
-- New query/snapshot demonstrates side-effects of previous query.
SELECT * FROM withz ORDER BY k;
k | v
----+------------------
0 | insert
1 | 1 v, now update
2 | insert
3 | insert
4 | 4 v, now update
5 | insert
6 | insert
7 | 7 v, now update
8 | insert
9 | insert
10 | 10 v, now update
11 | insert
12 | insert
13 | 13 v, now update
14 | insert
15 | insert
16 | 16 v, now update
(17 rows)
--
-- Ensure subqueries within the update clause work, even if they
-- reference outside values
--
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE withz.k = (SELECT a FROM aa);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b)
INSERT INTO withz VALUES(1, 'insert')
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)
INSERT INTO withz VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 ))
ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
-- Update a row more than once, in different parts of a wCTE. That is
-- an allowed, presumably very rare, edge case, but since it was
-- broken in the past, having a test seems worthwhile.
WITH simpletup AS (
SELECT 2 k, 'Green' v),
upsert_cte AS (
INSERT INTO withz VALUES(2, 'Blue') ON CONFLICT (k) DO
UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = withz.k)
RETURNING k, v)
INSERT INTO withz VALUES(2, 'Red') ON CONFLICT (k) DO
UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = withz.k)
RETURNING k, v;
k | v
---+---
(0 rows)
DROP TABLE withz;
-- check that run to completion happens in proper ordering
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 3);
CREATE TEMPORARY TABLE yy (a INTEGER);
WITH RECURSIVE t1 AS (
INSERT INTO y SELECT * FROM y RETURNING *
), t2 AS (
INSERT INTO yy SELECT * FROM t1 RETURNING *
)
SELECT 1;
?column?
----------
1
(1 row)
SELECT * FROM y;
a
---
1
2
3
1
2
3
(6 rows)
SELECT * FROM yy;
a
---
1
2
3
(3 rows)
WITH RECURSIVE t1 AS (
INSERT INTO yy SELECT * FROM t2 RETURNING *
), t2 AS (
INSERT INTO y SELECT * FROM y RETURNING *
)
SELECT 1;
?column?
----------
1
(1 row)
SELECT * FROM y;
a
---
1
2
3
1
2
3
1
2
3
1
2
3
(12 rows)
SELECT * FROM yy;
a
---
1
2
3
1
2
3
1
2
3
(9 rows)
-- triggers
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 10);
CREATE FUNCTION y_trigger() RETURNS trigger AS $$
begin
raise notice 'y_trigger: a = %', new.a;
return new;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(21),
(22),
(23)
RETURNING *
)
SELECT * FROM t;
NOTICE: y_trigger: a = 21
NOTICE: y_trigger: a = 22
NOTICE: y_trigger: a = 23
a
----
21
22
23
(3 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
(13 rows)
DROP TRIGGER y_trig ON y;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(31),
(32),
(33)
RETURNING *
)
SELECT * FROM t LIMIT 1;
NOTICE: y_trigger: a = 31
NOTICE: y_trigger: a = 32
NOTICE: y_trigger: a = 33
a
----
31
(1 row)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
31
32
33
(16 rows)
DROP TRIGGER y_trig ON y;
CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
begin
raise notice 'y_trigger';
return null;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
EXECUTE PROCEDURE y_trigger();
WITH t AS (
INSERT INTO y
VALUES
(41),
(42),
(43)
RETURNING *
)
SELECT * FROM t;
NOTICE: y_trigger
a
----
41
42
43
(3 rows)
SELECT * FROM y;
a
----
1
2
3
4
5
6
7
8
9
10
21
22
23
31
32
33
41
42
43
(19 rows)
DROP TRIGGER y_trig ON y;
DROP FUNCTION y_trigger();
-- WITH attached to inherited UPDATE or DELETE
CREATE TEMP TABLE parent ( id int, val text );
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
INSERT INTO parent VALUES ( 1, 'p1' );
INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
UPDATE parent SET id = id + totalid FROM rcte;
SELECT * FROM parent;
id | val
----+-----
72 | p1
82 | c11
83 | c12
94 | c21
95 | c22
(5 rows)
WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
UPDATE parent SET id = id + newid FROM wcte;
SELECT * FROM parent;
id | val
-----+-----
114 | p1
42 | new
124 | c11
125 | c12
136 | c21
137 | c22
(6 rows)
WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
DELETE FROM parent USING rcte WHERE id = maxid;
SELECT * FROM parent;
id | val
-----+-----
114 | p1
42 | new
124 | c11
125 | c12
136 | c21
(5 rows)
WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
DELETE FROM parent USING wcte WHERE id = newid;
SELECT * FROM parent;
id | val
-----+------
114 | p1
124 | c11
125 | c12
136 | c21
42 | new2
(5 rows)
-- check EXPLAIN VERBOSE for a wCTE with RETURNING
EXPLAIN (VERBOSE, COSTS OFF)
WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
DELETE FROM a USING wcte WHERE aa = q2;
QUERY PLAN
----------------------------------------------------
Delete on public.a
Delete on public.a
Delete on public.b
Delete on public.c
Delete on public.d
CTE wcte
-> Insert on public.int8_tbl
Output: int8_tbl.q2
-> Result
Output: '42'::bigint, '47'::bigint
-> Nested Loop
Output: a.ctid, wcte.*
Join Filter: (a.aa = wcte.q2)
-> Seq Scan on public.a
Output: a.ctid, a.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: b.ctid, wcte.*
Join Filter: (b.aa = wcte.q2)
-> Seq Scan on public.b
Output: b.ctid, b.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: c.ctid, wcte.*
Join Filter: (c.aa = wcte.q2)
-> Seq Scan on public.c
Output: c.ctid, c.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-> Nested Loop
Output: d.ctid, wcte.*
Join Filter: (d.aa = wcte.q2)
-> Seq Scan on public.d
Output: d.ctid, d.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
(38 rows)
-- error cases
-- data-modifying WITH tries to use its own output
WITH RECURSIVE t AS (
INSERT INTO y
SELECT * FROM t
)
VALUES(FALSE);
ERROR: recursive query "t" must not contain data-modifying statements
LINE 1: WITH RECURSIVE t AS (
^
-- no RETURNING in a referenced data-modifying WITH
WITH t AS (
INSERT INTO y VALUES(0)
)
SELECT * FROM t;
ERROR: WITH query "t" does not have a RETURNING clause
LINE 4: SELECT * FROM t;
^
-- data-modifying WITH allowed only at the top level
SELECT * FROM (
WITH t AS (UPDATE y SET a=a+1 RETURNING *)
SELECT * FROM t
) ss;
ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
^
-- most variants of rules aren't allowed
CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
WITH t AS (
INSERT INTO y VALUES(0)
)
VALUES(FALSE);
ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
DROP RULE y_rule ON y;
-- check that parser lookahead for WITH doesn't cause any odd behavior
create table foo (with baz); -- fail, WITH is a reserved word
ERROR: syntax error at or near "with"
LINE 1: create table foo (with baz);
^
create table foo (with ordinality); -- fail, WITH is a reserved word
ERROR: syntax error at or near "with"
LINE 1: create table foo (with ordinality);
^
with ordinality as (select 1 as x) select * from ordinality;
x
---
1
(1 row)
-- check sane response to attempt to modify CTE relation
WITH test AS (SELECT 42) INSERT INTO test VALUES (1);
ERROR: relation "test" does not exist
LINE 1: WITH test AS (SELECT 42) INSERT INTO test VALUES (1);
^
-- check response to attempt to modify table with same name as a CTE (perhaps
-- surprisingly it works, because CTEs don't hide tables from data-modifying
-- statements)
create temp table test (i int);
with test as (select 42) insert into test select * from test;
select * from test;
i
----
42
(1 row)
drop table test;
[text/plain] window.out (116.5K, ../20180725054236.GB17411@fetter.org/5-window.out)
download
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-hackers@postgresql.org
Cc: david@fetter.org, andrew@tao11.riddles.org.uk
Subject: Re: Early WIP/PoC for inlining CTEs
In-Reply-To: <20180725054236.GB17411@fetter.org>
* 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