CREATE EXTENSION pg_ivm;
GRANT ALL ON SCHEMA public TO public;
-- create a table to use as a basis for views and materialized views in various combinations
CREATE TABLE mv_base_a (x int, i int, y int, j int);
CREATE TABLE mv_base_b (x int, i int, y int, k int);
-- test for base tables with dropped columns
ALTER TABLE mv_base_a DROP COLUMN x;
ALTER TABLE mv_base_a DROP COLUMN y;
ALTER TABLE mv_base_b DROP COLUMN x;
ALTER TABLE mv_base_b DROP COLUMN y;
INSERT INTO mv_base_a VALUES
  (1,10),
  (2,20),
  (3,30),
  (4,40),
  (5,50);
INSERT INTO mv_base_b VALUES
  (1,101),
  (2,102),
  (3,103),
  (4,104);
SELECT pgivm.create_immv('mv_ivm_1', 'SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i)');
NOTICE:  could not create an index on immv "mv_ivm_1" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

-- immediate maintenance
BEGIN;
INSERT INTO mv_base_b VALUES(5,105);
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
 5 | 50 | 105
(5 rows)

UPDATE mv_base_a SET j = 0 WHERE i = 1;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 |  0 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
 5 | 50 | 105
(5 rows)

DELETE FROM mv_base_b WHERE (i,k) = (5,105);
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 |  0 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

ROLLBACK;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

-- test for renaming column name to camel style
BEGIN;
ALTER TABLE mv_base_a RENAME i TO "I";
ALTER TABLE mv_base_a RENAME j TO "J";
UPDATE mv_base_a SET "J" = 0 WHERE "I" = 1;
SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
 i | j  |  k  
---+----+-----
 1 |  0 | 101
 2 | 20 | 102
 3 | 30 | 103
 4 | 40 | 104
(4 rows)

ROLLBACK;
-- TRUNCATE a base table in join views
BEGIN;
TRUNCATE mv_base_a;
SELECT * FROM mv_ivm_1;
 i | j | k 
---+---+---
(0 rows)

ROLLBACK;
BEGIN;
TRUNCATE mv_base_b;
SELECT * FROM mv_ivm_1;
 i | j | k 
---+---+---
(0 rows)

ROLLBACK;
-- some query syntax
BEGIN;
CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'
       AS 'SELECT 1' IMMUTABLE;
SELECT pgivm.create_immv('mv_ivm_func', 'SELECT * FROM ivm_func()');
NOTICE:  could not create an index on immv "mv_ivm_func" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           1
(1 row)

SELECT pgivm.create_immv('mv_ivm_no_tbl', 'SELECT 1');
NOTICE:  could not create an index on immv "mv_ivm_no_tbl" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           1
(1 row)

ROLLBACK;
-- result of materialized view have DISTINCT clause or the duplicate result.
BEGIN;
SELECT pgivm.create_immv('mv_ivm_duplicate', 'SELECT j FROM mv_base_a');
NOTICE:  could not create an index on immv "mv_ivm_duplicate" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           5
(1 row)

SELECT pgivm.create_immv('mv_ivm_distinct', 'SELECT DISTINCT j FROM mv_base_a');
NOTICE:  created index "mv_ivm_distinct_index" on immv "mv_ivm_distinct"
 create_immv 
-------------
           5
(1 row)

INSERT INTO mv_base_a VALUES(6,20);
SELECT * FROM mv_ivm_duplicate ORDER BY 1;
 j  
----
 10
 20
 20
 30
 40
 50
(6 rows)

SELECT * FROM mv_ivm_distinct ORDER BY 1;
 j  | __ivm_count__ 
----+---------------
 10 |             1
 20 |             2
 30 |             1
 40 |             1
 50 |             1
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (2,20);
SELECT * FROM mv_ivm_duplicate ORDER BY 1;
 j  
----
 10
 20
 30
 40
 50
(5 rows)

SELECT * FROM mv_ivm_distinct ORDER BY 1;
 j  | __ivm_count__ 
----+---------------
 10 |             1
 20 |             1
 30 |             1
 40 |             1
 50 |             1
(5 rows)

ROLLBACK;
-- support SUM(), COUNT() and AVG() aggregate functions
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg', 'SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_agg_index" on immv "mv_ivm_agg"
 create_immv 
-------------
           5
(1 row)

SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 | 10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 |  20 |     1 | 20.0000000000000000 |                 1 |                 1 |              20 |             1
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

INSERT INTO mv_base_a VALUES(2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 | 10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 | 120 |     2 | 60.0000000000000000 |                 2 |                 2 |             120 |             2
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg          | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+----------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 |  10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 | 220 |     2 | 110.0000000000000000 |                 2 |                 2 |             220 |             2
 3 |  30 |     1 |  30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 |  40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 |  50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (2,200);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 | 10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 |  20 |     1 | 20.0000000000000000 |                 1 |                 1 |              20 |             1
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

ROLLBACK;
-- support COUNT(*) aggregate function
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg', 'SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_agg_index" on immv "mv_ivm_agg"
 create_immv 
-------------
           5
(1 row)

SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
 i | sum | count | __ivm_count_sum__ | __ivm_count__ 
---+-----+-------+-------------------+---------------
 1 |  10 |     1 |                 1 |             1
 2 |  20 |     1 |                 1 |             1
 3 |  30 |     1 |                 1 |             1
 4 |  40 |     1 |                 1 |             1
 5 |  50 |     1 |                 1 |             1
(5 rows)

INSERT INTO mv_base_a VALUES(2,100);
SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
 i | sum | count | __ivm_count_sum__ | __ivm_count__ 
---+-----+-------+-------------------+---------------
 1 |  10 |     1 |                 1 |             1
 2 | 120 |     2 |                 2 |             2
 3 |  30 |     1 |                 1 |             1
 4 |  40 |     1 |                 1 |             1
 5 |  50 |     1 |                 1 |             1
(5 rows)

ROLLBACK;
-- TRUNCATE a base table in aggregate views
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg', 'SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_agg_index" on immv "mv_ivm_agg"
 create_immv 
-------------
           5
(1 row)

TRUNCATE mv_base_a;
SELECT sum, count FROM mv_ivm_agg;
 sum | count 
-----+-------
(0 rows)

SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
 i | sum | count 
---+-----+-------
(0 rows)

ROLLBACK;
-- support aggregate functions without GROUP clause
BEGIN;
SELECT pgivm.create_immv('mv_ivm_group',  'SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a');
 create_immv 
-------------
           1
(1 row)

SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 150 |     5 | 30.0000000000000000 |                 5 |                 5 |             150 |             5
(1 row)

INSERT INTO mv_base_a VALUES(6,60);
SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 210 |     6 | 35.0000000000000000 |                 6 |                 6 |             210 |             6
(1 row)

DELETE FROM mv_base_a;
SELECT * FROM mv_ivm_group ORDER BY 1;
 sum | count | avg | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
-----+-------+-----+-------------------+-------------------+-----------------+---------------
     |     0 |     |                 0 |                 0 |                 |             0
(1 row)

ROLLBACK;
-- TRUNCATE a base table in aggregate views without GROUP clause
BEGIN;
SELECT pgivm.create_immv('mv_ivm_group', 'SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a');
 create_immv 
-------------
           1
(1 row)

TRUNCATE mv_base_a;
SELECT sum, count, avg FROM mv_ivm_group;
 sum | count | avg 
-----+-------+-----
     |     0 |    
(1 row)

SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
 sum | count | avg 
-----+-------+-----
     |     0 |    
(1 row)

ROLLBACK;
-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.
BEGIN;
SELECT pgivm.create_immv('mv_ivm_avg_bug', 'SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i');
NOTICE:  created index "mv_ivm_avg_bug_index" on immv "mv_ivm_avg_bug"
 create_immv 
-------------
           5
(1 row)

SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 | 10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 |  20 |     1 | 20.0000000000000000 |                 1 |                 1 |              20 |             1
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

INSERT INTO mv_base_a VALUES
  (1,0),
  (1,0),
  (2,30),
  (2,30);
SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     3 |  3.3333333333333333 |                 3 |                 3 |              10 |             3
 2 |  80 |     3 | 26.6666666666666667 |                 3 |                 3 |              80 |             3
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) = (1,0);
DELETE FROM mv_base_a WHERE (i,j) = (2,30);
SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
 i | sum | count |         avg         | __ivm_count_sum__ | __ivm_count_avg__ | __ivm_sum_avg__ | __ivm_count__ 
---+-----+-------+---------------------+-------------------+-------------------+-----------------+---------------
 1 |  10 |     1 | 10.0000000000000000 |                 1 |                 1 |              10 |             1
 2 |  20 |     1 | 20.0000000000000000 |                 1 |                 1 |              20 |             1
 3 |  30 |     1 | 30.0000000000000000 |                 1 |                 1 |              30 |             1
 4 |  40 |     1 | 40.0000000000000000 |                 1 |                 1 |              40 |             1
 5 |  50 |     1 | 50.0000000000000000 |                 1 |                 1 |              50 |             1
(5 rows)

ROLLBACK;
-- support MIN(), MAX() aggregate functions
BEGIN;
SELECT pgivm.create_immv('mv_ivm_min_max(i, min_j, max_j)', 'SELECT i, MIN(j), MAX(j)  FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_min_max_index" on immv "mv_ivm_min_max"
 create_immv 
-------------
           5
(1 row)

SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
 i | min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
---+-------+-------+---------------------+---------------------+---------------
 1 |    10 |    10 |                   1 |                   1 |             1
 2 |    20 |    20 |                   1 |                   1 |             1
 3 |    30 |    30 |                   1 |                   1 |             1
 4 |    40 |    40 |                   1 |                   1 |             1
 5 |    50 |    50 |                   1 |                   1 |             1
(5 rows)

INSERT INTO mv_base_a VALUES
  (1,11), (1,12),
  (2,21), (2,22),
  (3,31), (3,32),
  (4,41), (4,42),
  (5,51), (5,52);
SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
 i | min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
---+-------+-------+---------------------+---------------------+---------------
 1 |    10 |    12 |                   3 |                   3 |             3
 2 |    20 |    22 |                   3 |                   3 |             3
 3 |    30 |    32 |                   3 |                   3 |             3
 4 |    40 |    42 |                   3 |                   3 |             3
 5 |    50 |    52 |                   3 |                   3 |             3
(5 rows)

DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32));
SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
 i | min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
---+-------+-------+---------------------+---------------------+---------------
 1 |    11 |    12 |                   2 |                   2 |             2
 2 |    20 |    22 |                   2 |                   2 |             2
 3 |    30 |    31 |                   2 |                   2 |             2
 4 |    40 |    42 |                   3 |                   3 |             3
 5 |    50 |    52 |                   3 |                   3 |             3
(5 rows)

ROLLBACK;
-- support MIN(), MAX() aggregate functions without GROUP clause
BEGIN;
SELECT pgivm.create_immv('mv_ivm_min_max(min_j, max_j)', 'SELECT MIN(j), MAX(j)  FROM mv_base_a');
 create_immv 
-------------
           1
(1 row)

SELECT * FROM mv_ivm_min_max;
 min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
-------+-------+---------------------+---------------------+---------------
    10 |    50 |                   5 |                   5 |             5
(1 row)

INSERT INTO mv_base_a VALUES
  (0,0), (6,60), (7,70);
SELECT * FROM mv_ivm_min_max;
 min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
-------+-------+---------------------+---------------------+---------------
     0 |    70 |                   8 |                   8 |             8
(1 row)

DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70));
SELECT * FROM mv_ivm_min_max;
 min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
-------+-------+---------------------+---------------------+---------------
    10 |    60 |                   6 |                   6 |             6
(1 row)

DELETE FROM mv_base_a;
SELECT * FROM mv_ivm_min_max;
 min_j | max_j | __ivm_count_min_j__ | __ivm_count_max_j__ | __ivm_count__ 
-------+-------+---------------------+---------------------+---------------
       |       |                   0 |                   0 |             0
(1 row)

ROLLBACK;
-- Test MIN/MAX after search_path change
BEGIN;
SELECT pgivm.create_immv('mv_ivm_min', 'SELECT MIN(j) FROM mv_base_a');
 create_immv 
-------------
           1
(1 row)

SELECT * FROM mv_ivm_min ORDER BY 1,2,3;
 min | __ivm_count_min__ | __ivm_count__ 
-----+-------------------+---------------
  10 |                 5 |             5
(1 row)

CREATE SCHEMA myschema;
GRANT ALL ON SCHEMA myschema TO public;
CREATE TABLE myschema.mv_base_a (j int);
INSERT INTO myschema.mv_base_a VALUES (1);
DELETE FROM mv_base_a WHERE (i,j) = (1,10);
SELECT * FROM mv_ivm_min ORDER BY 1,2,3;
 min | __ivm_count_min__ | __ivm_count__ 
-----+-------------------+---------------
  20 |                 4 |             4
(1 row)

SET search_path TO myschema,public,pg_catalog;
DELETE FROM public.mv_base_a WHERE (i,j) = (2,20);
SELECT * FROM mv_ivm_min ORDER BY 1,2,3;
 min | __ivm_count_min__ | __ivm_count__ 
-----+-------------------+---------------
  30 |                 3 |             3
(1 row)

ROLLBACK;
-- aggregate views with column names specified
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg(a)', 'SELECT i, SUM(j) FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_agg_index" on immv "mv_ivm_agg"
 create_immv 
-------------
           5
(1 row)

INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);
UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);
DELETE FROM mv_base_a WHERE (i,j) = (3,30);
SELECT * FROM mv_ivm_agg ORDER BY 1,2;
 a | sum  | __ivm_count_sum__ | __ivm_count__ 
---+------+-------------------+---------------
 1 |  110 |                 2 |             2
 2 | 2200 |                 2 |             2
 3 |  300 |                 1 |             1
 4 |   40 |                 1 |             1
 5 |   50 |                 1 |             1
(5 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg(a,b)', 'SELECT i, SUM(j) FROM mv_base_a GROUP BY i');
NOTICE:  created index "mv_ivm_agg_index" on immv "mv_ivm_agg"
 create_immv 
-------------
           5
(1 row)

INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);
UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);
DELETE FROM mv_base_a WHERE (i,j) = (3,30);
SELECT * FROM mv_ivm_agg ORDER BY 1,2;
 a |  b   | __ivm_count_b__ | __ivm_count__ 
---+------+-----------------+---------------
 1 |  110 |               2 |             2
 2 | 2200 |               2 |             2
 3 |  300 |               1 |             1
 4 |   40 |               1 |             1
 5 |   50 |               1 |             1
(5 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_ivm_agg(a,b,c)', 'SELECT i, SUM(j) FROM mv_base_a GROUP BY i');
ERROR:  too many column names were specified
ROLLBACK;
-- support self join view and multiple change on the same table
BEGIN;
CREATE TABLE base_t (i int, v int);
INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);
SELECT pgivm.create_immv('mv_self(v1, v2)',
 'SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i');
NOTICE:  could not create an index on immv "mv_self" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           3
(1 row)

SELECT * FROM mv_self ORDER BY v1;
 v1 | v2 
----+----
 10 | 10
 20 | 20
 30 | 30
(3 rows)

INSERT INTO base_t VALUES (4,40);
DELETE FROM base_t WHERE i = 1;
UPDATE base_t SET v = v*10 WHERE i=2;
SELECT * FROM mv_self ORDER BY v1;
 v1  | v2  
-----+-----
  30 |  30
  40 |  40
 200 | 200
(3 rows)

WITH
 ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),
 ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),
 upd_t AS (UPDATE base_t SET v = v + 100  RETURNING 1),
 dlt_t AS (DELETE FROM base_t WHERE i IN (4,5)  RETURNING 1)
SELECT NULL;
 ?column? 
----------
 
(1 row)

SELECT * FROM mv_self ORDER BY v1;
 v1  | v2  
-----+-----
  50 |  50
  60 |  60
 130 | 130
 300 | 300
(4 rows)

--- with sub-transactions
SAVEPOINT p1;
INSERT INTO base_t VALUES (7,70);
RELEASE SAVEPOINT p1;
INSERT INTO base_t VALUES (7,77);
SELECT * FROM mv_self ORDER BY v1, v2;
 v1  | v2  
-----+-----
  50 |  50
  60 |  60
  70 |  70
  70 |  77
  77 |  70
  77 |  77
 130 | 130
 300 | 300
(8 rows)

ROLLBACK;
-- support simultaneous table changes
BEGIN;
CREATE TABLE base_r (i int, v int);
CREATE TABLE base_s (i int, v int);
INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30);
INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300);
SELECT pgivm.create_immv('mv(v1, v2)', 'SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i)');;
NOTICE:  could not create an index on immv "mv" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           3
(1 row)

SELECT * FROM mv ORDER BY v1;
 v1 | v2  
----+-----
 10 | 100
 20 | 200
 30 | 300
(3 rows)

WITH
 ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1),
 ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1),
 ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1),
 upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1),
 dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1)
SELECT NULL;
 ?column? 
----------
 
(1 row)

SELECT * FROM mv ORDER BY v1;
  v1  | v2  
------+-----
   10 | 100
   11 | 100
 1020 | 200
 1020 | 222
(4 rows)

-- support foreign reference constraints
BEGIN;
WARNING:  there is already a transaction in progress
CREATE TABLE ri1 (i int PRIMARY KEY);
CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int);
INSERT INTO ri1 VALUES (1),(2),(3);
INSERT INTO ri2 VALUES (1),(2),(3);
SELECT pgivm.create_immv('mv_ri(i1, i2)', 'SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i)');
NOTICE:  created index "mv_ri_index" on immv "mv_ri"
 create_immv 
-------------
           3
(1 row)

SELECT * FROM mv_ri ORDER BY i1;
 i1 | i2 
----+----
  1 |  1
  2 |  2
  3 |  3
(3 rows)

UPDATE ri1 SET i=10 where i=1;
DELETE FROM ri1 WHERE i=2;
SELECT * FROM mv_ri ORDER BY i2;
 i1 | i2 
----+----
  3 |  3
 10 | 10
(2 rows)

ROLLBACK;
-- support subquery for using EXISTS()
BEGIN;
SELECT pgivm.create_immv('mv_ivm_exists_subquery', 'SELECT a.i, a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i)');
NOTICE:  could not create an index on immv "mv_ivm_exists_subquery" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

SELECT pgivm.create_immv('mv_ivm_exists_subquery2', 'SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) AND a.i > 2');
NOTICE:  could not create an index on immv "mv_ivm_exists_subquery2" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           2
(1 row)

SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i | j  | __ivm_exists_count_0__ 
---+----+------------------------
 1 | 10 |                      1
 2 | 20 |                      1
 3 | 30 |                      1
 4 | 40 |                      1
(4 rows)

SELECT * FROM mv_ivm_exists_subquery2 ORDER BY i, j;
 i | j  | __ivm_exists_count_0__ 
---+----+------------------------
 3 | 30 |                      1
 4 | 40 |                      1
(2 rows)

INSERT INTO mv_base_a VALUES(1,10),(6,60),(3,30),(3,300);
SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 1 |  10 |                      1
 1 |  10 |                      1
 2 |  20 |                      1
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(7 rows)

SELECT * FROM mv_ivm_exists_subquery2 ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(4 rows)

INSERT INTO mv_base_b VALUES(1,101);
INSERT INTO mv_base_b VALUES(1,111);
INSERT INTO mv_base_b VALUES(2,102);
INSERT INTO mv_base_b VALUES(6,106);
SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 1 |  10 |                      3
 1 |  10 |                      3
 2 |  20 |                      2
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
 6 |  60 |                      1
(8 rows)

SELECT * FROM mv_ivm_exists_subquery2 ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
 6 |  60 |                      1
(5 rows)

UPDATE mv_base_a SET i = 1 WHERE j =60;
UPDATE mv_base_b SET i = 10  WHERE k = 101;
UPDATE mv_base_b SET k = 1002 WHERE k = 102;
SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 1 |  10 |                      1
 1 |  10 |                      1
 1 |  60 |                      1
 2 |  20 |                      2
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(8 rows)

SELECT * FROM mv_ivm_exists_subquery2 ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(4 rows)

DELETE FROM mv_base_a WHERE (i,j) = (1,60);
DELETE FROM mv_base_b WHERE i = 2;
SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 1 |  10 |                      1
 1 |  10 |                      1
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(6 rows)

SELECT * FROM mv_ivm_exists_subquery2 ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(4 rows)

--- EXISTS subquery with tuple duplication and DISTINCT
SELECT pgivm.create_immv('mv_ivm_exists_subquery_distinct', 'SELECT DISTINCT a.i, a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i)');
NOTICE:  created index "mv_ivm_exists_subquery_distinct_index" on immv "mv_ivm_exists_subquery_distinct"
 create_immv 
-------------
           4
(1 row)

DELETE FROM mv_base_b WHERE i = 1 or i = 3;
INSERT INTO mv_base_b VALUES (1,100), (3,300);
SELECT * FROM mv_ivm_exists_subquery ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ 
---+-----+------------------------
 1 |  10 |                      1
 1 |  10 |                      1
 3 |  30 |                      1
 3 |  30 |                      1
 3 | 300 |                      1
 4 |  40 |                      1
(6 rows)

SELECT * FROM mv_ivm_exists_subquery_distinct ORDER BY i, j;
 i |  j  | __ivm_exists_count_0__ | __ivm_count__ 
---+-----+------------------------+---------------
 1 |  10 |                      1 |             2
 3 |  30 |                      1 |             2
 3 | 300 |                      1 |             1
 4 |  40 |                      1 |             1
(4 rows)

ROLLBACK;
-- support simple subquery in FROM clause
BEGIN;
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.i,a.j FROM mv_base_a a,( SELECT * FROM mv_base_b) b WHERE a.i = b.i');
NOTICE:  could not create an index on immv "mv_ivm_subquery" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_ivm_subquery ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
-- disallow non-simple subqueries
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i');
ERROR:  aggregate functions in nested query are not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.i,a.j FROM mv_base_a a, (SELECT DISTINCT i FROM mv_base_b) b WHERE a.i = b.i');
ERROR:  DISTINCT clause in nested query are not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 )');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT i,j, (SELECT k FROM mv_base_b LIMIT 1) FROM mv_base_a a');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT i,j, (SELECT k FROM mv_base_b LIMIT 1) + 1 FROM mv_base_a a');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT * FROM generate_series(1, (SELECT k FROM mv_base_b LIMIT 1)) AS v');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i)');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  targetlist must contain vars that are referred to in EXISTS subquery
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 2');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  OR or NOT conditions and EXISTS condition can not be used together
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_a a2 WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a2.i = b.i))');
ERROR:  nested sublink is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT EXISTS(SELECT 1 from mv_base_b) FROM mv_base_a a');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT false OR EXISTS(SELECT 1 FROM mv_base_a) FROM mv_base_b');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  OR or NOT conditions and EXISTS condition can not be used together
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT * FROM generate_series(1, CASE EXISTS(SELECT 1 FROM mv_base_a) WHEN true THEN 100 ELSE 10 END), mv_base_b');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT * FROM mv_base_a a WHERE CASE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) WHEN true THEN false ELSE true END');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
SELECT pgivm.create_immv('mv_ivm_subquery', 'SELECT * FROM mv_base_a a WHERE true and CASE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) WHEN true THEN false ELSE true END');
ERROR:  this query is not allowed on incrementally maintainable materialized view
HINT:  sublink only supports subquery with EXISTS clause in WHERE clause
-- support join subquery in FROM clause
BEGIN;
SELECT pgivm.create_immv('mv_ivm_join_subquery', 'SELECT i, j, k FROM ( SELECT i, a.j, b.k FROM mv_base_b b INNER JOIN mv_base_a a USING(i)) tmp');
NOTICE:  could not create an index on immv "mv_ivm_join_subquery" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

WITH
 ai AS (INSERT INTO mv_base_a VALUES (1,11),(2,22) RETURNING 0),
 bi AS (INSERT INTO mv_base_b VALUES (1,111),(3,133) RETURNING 0),
 bd AS (DELETE FROM mv_base_b WHERE i = 4 RETURNING 0)
SELECT;
--
(1 row)

SELECT * FROM mv_ivm_join_subquery ORDER BY i,j,k;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 1 | 10 | 111
 1 | 11 | 101
 1 | 11 | 111
 2 | 20 | 102
 2 | 22 | 102
 3 | 30 | 103
 3 | 30 | 133
(8 rows)

ROLLBACK;
BEGIN;
-- nested subquery
SELECT pgivm.create_immv('mv_ivm_join_subquery', 'SELECT i, j, k FROM ( SELECT i, a.j, b.k FROM mv_base_b b INNER JOIN (SELECT * FROM mv_base_a) a USING(i)) tmp');
NOTICE:  could not create an index on immv "mv_ivm_join_subquery" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

WITH
 ai AS (INSERT INTO mv_base_a VALUES (1,11),(2,22) RETURNING 0),
 bi AS (INSERT INTO mv_base_b VALUES (1,111),(3,133) RETURNING 0),
 bd AS (DELETE FROM mv_base_b WHERE i = 4 RETURNING 0)
SELECT;
--
(1 row)

SELECT * FROM mv_ivm_join_subquery ORDER BY i,j,k;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 1 | 10 | 111
 1 | 11 | 101
 1 | 11 | 111
 2 | 20 | 102
 2 | 22 | 102
 3 | 30 | 103
 3 | 30 | 133
(8 rows)

ROLLBACK;
-- support simple CTE
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_cte ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'WITH a AS (SELECT * FROM mv_base_a), b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM a, b WHERE a.i = b.i');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_cte ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'WITH b AS ( SELECT * FROM mv_base_b) SELECT v.i,v.j FROM (WITH a AS (SELECT * FROM mv_base_a) SELECT a.i,a.j FROM a, b WHERE a.i = b.i) v');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_cte ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'SELECT * FROM (WITH a AS (SELECT * FROM mv_base_a), b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM a, b WHERE a.i = b.i) v');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_cte ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'WITH b AS ( SELECT * FROM (SELECT * FROM mv_base_b) b2) SELECT v.i,v.j FROM (WITH a AS (SELECT * FROM mv_base_a) SELECT a.i,a.j FROM a, b WHERE a.i = b.i) v');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

INSERT INTO mv_base_a VALUES(2,20);
INSERT INTO mv_base_b VALUES(3,300);
SELECT * FROM mv_cte ORDER BY i,j;
 i | j  
---+----
 1 | 10
 2 | 20
 2 | 20
 3 | 30
 3 | 30
 4 | 40
(6 rows)

ROLLBACK;
BEGIN;
SELECT pgivm.create_immv('mv_cte',
    'WITH x AS ( SELECT i, a.j, b.k FROM mv_base_b b INNER JOIN mv_base_a a USING(i)) SELECT * FROM x');
NOTICE:  could not create an index on immv "mv_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

WITH
 ai AS (INSERT INTO mv_base_a VALUES (1,11),(2,22) RETURNING 0),
 bi AS (INSERT INTO mv_base_b VALUES (1,111),(3,133) RETURNING 0),
 bd AS (DELETE FROM mv_base_b WHERE i = 4 RETURNING 0)
SELECT;
--
(1 row)

SELECT * FROM mv_cte ORDER BY i,j,k;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 1 | 10 | 111
 1 | 11 | 101
 1 | 11 | 111
 2 | 20 | 102
 2 | 22 | 102
 3 | 30 | 103
 3 | 30 | 133
(8 rows)

ROLLBACK;
-- nested CTE
BEGIN;
SELECT pgivm.create_immv('mv_ivm_nested_cte', 'WITH v AS ( WITH a AS (SELECT * FROM mv_base_a) SELECT i, a.j, b.k FROM mv_base_b b INNER JOIN a USING(i)) SELECT * FROM v');
NOTICE:  could not create an index on immv "mv_ivm_nested_cte" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           4
(1 row)

WITH
 ai AS (INSERT INTO mv_base_a VALUES (1,11),(2,22) RETURNING 0),
 bi AS (INSERT INTO mv_base_b VALUES (1,111),(3,133) RETURNING 0),
 bd AS (DELETE FROM mv_base_b WHERE i = 4 RETURNING 0)
SELECT;
--
(1 row)

SELECT * FROM mv_ivm_nested_cte ORDER BY i,j,k;
 i | j  |  k  
---+----+-----
 1 | 10 | 101
 1 | 10 | 111
 1 | 11 | 101
 1 | 11 | 111
 2 | 20 | 102
 2 | 22 | 102
 3 | 30 | 103
 3 | 30 | 133
(8 rows)

ROLLBACK;
-- Multiply-referenced CTE
BEGIN;
CREATE TABLE base_t (i int, v int);
INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);
SELECT pgivm.create_immv('mv_cte_multi(v1, v2)',
 'WITH t AS (SELECT * FROM base_t) SELECT t1.v, t2.v FROM t AS t1 JOIN t AS t2 ON t1.i = t2.i');
NOTICE:  could not create an index on immv "mv_cte_multi" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           3
(1 row)

SELECT * FROM mv_cte_multi ORDER BY v1;
 v1 | v2 
----+----
 10 | 10
 20 | 20
 30 | 30
(3 rows)

INSERT INTO base_t VALUES (4,40);
DELETE FROM base_t WHERE i = 1;
UPDATE base_t SET v = v*10 WHERE i=2;
SELECT * FROM mv_cte_multi ORDER BY v1;
 v1  | v2  
-----+-----
  30 |  30
  40 |  40
 200 | 200
(3 rows)

WITH
 ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),
 ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),
 upd_t AS (UPDATE base_t SET v = v + 100  RETURNING 1),
 dlt_t AS (DELETE FROM base_t WHERE i IN (4,5)  RETURNING 1)
SELECT NULL;
 ?column? 
----------
 
(1 row)

SELECT * FROM mv_cte_multi ORDER BY v1;
 v1  | v2  
-----+-----
  50 |  50
  60 |  60
 130 | 130
 300 | 300
(4 rows)

ROLLBACK;
--- disallow not-simple CTE
SELECT pgivm.create_immv('mv_cte_fail', 'WITH b AS (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i');
ERROR:  aggregate functions in nested query are not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_cte_fail', 'WITH b AS (SELECT DISTINCT i FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i');
ERROR:  DISTINCT clause in nested query are not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_cte_fail', 'WITH a AS (SELECT i, j FROM mv_base_a) SELECT a.i,a.j FROM a WHERE EXISTS(WITH b AS (SELECT i FROM mv_base_b) SELECT 1 FROM b WHERE a.i = b.i)');
ERROR:  CTE in EXIST clause is not supported on incrementally maintainable materialized view
-- unreferenced CTE
SELECT pgivm.create_immv('mv_cte_fail', 'WITH b AS (SELECT * FROM mv_base_b) SELECT * FROM mv_base_a a');
ERROR:  Ureferenced WITH query is not supported on incrementally maintainable materialized view
-- views including NULL
BEGIN;
CREATE TABLE base_t (i int, v int);
INSERT INTO base_t VALUES (1,10),(2, NULL);
SELECT pgivm.create_immv('mv', 'SELECT * FROM base_t');
NOTICE:  could not create an index on immv "mv" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           2
(1 row)

SELECT * FROM mv ORDER BY i;
 i | v  
---+----
 1 | 10
 2 |   
(2 rows)

UPDATE base_t SET v = 20 WHERE i = 2;
SELECT * FROM mv ORDER BY i;
 i | v  
---+----
 1 | 10
 2 | 20
(2 rows)

ROLLBACK;
BEGIN;
CREATE TABLE base_t (i int);
SELECT pgivm.create_immv('mv', 'SELECT * FROM base_t');
NOTICE:  could not create an index on immv "mv" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           0
(1 row)

SELECT * FROM mv ORDER BY i;
 i 
---
(0 rows)

INSERT INTO base_t VALUES (1),(NULL);
SELECT * FROM mv ORDER BY i;
 i 
---
 1
  
(2 rows)

ROLLBACK;
BEGIN;
CREATE TABLE base_t (i int, v int);
INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);
SELECT pgivm.create_immv('mv', 'SELECT i, sum(v) FROM base_t GROUP BY i');
NOTICE:  created index "mv_index" on immv "mv"
 create_immv 
-------------
           2
(1 row)

SELECT * FROM mv ORDER BY i;
 i | sum | __ivm_count_sum__ | __ivm_count__ 
---+-----+-------------------+---------------
 1 |  30 |                 2 |             2
   |   3 |                 2 |             2
(2 rows)

UPDATE base_t SET v = v * 10;
SELECT * FROM mv ORDER BY i;
 i | sum | __ivm_count_sum__ | __ivm_count__ 
---+-----+-------------------+---------------
 1 | 300 |                 2 |             2
   |  30 |                 2 |             2
(2 rows)

ROLLBACK;
BEGIN;
CREATE TABLE base_t (i int, v int);
INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5);
SELECT pgivm.create_immv('mv', 'SELECT i, min(v), max(v) FROM base_t GROUP BY i');
NOTICE:  created index "mv_index" on immv "mv"
 create_immv 
-------------
           1
(1 row)

SELECT * FROM mv ORDER BY i;
 i | min | max | __ivm_count_min__ | __ivm_count_max__ | __ivm_count__ 
---+-----+-----+-------------------+-------------------+---------------
   |   1 |   5 |                 5 |                 5 |             5
(1 row)

DELETE FROM base_t WHERE v = 1;
SELECT * FROM mv ORDER BY i;
 i | min | max | __ivm_count_min__ | __ivm_count_max__ | __ivm_count__ 
---+-----+-----+-------------------+-------------------+---------------
   |   2 |   5 |                 4 |                 4 |             4
(1 row)

DELETE FROM base_t WHERE v = 3;
SELECT * FROM mv ORDER BY i;
 i | min | max | __ivm_count_min__ | __ivm_count_max__ | __ivm_count__ 
---+-----+-----+-------------------+-------------------+---------------
   |   2 |   5 |                 3 |                 3 |             3
(1 row)

DELETE FROM base_t WHERE v = 5;
SELECT * FROM mv ORDER BY i;
 i | min | max | __ivm_count_min__ | __ivm_count_max__ | __ivm_count__ 
---+-----+-----+-------------------+-------------------+---------------
   |   2 |   4 |                 2 |                 2 |             2
(1 row)

ROLLBACK;
-- IMMV containing user defined type
BEGIN;
CREATE TYPE mytype;
CREATE FUNCTION mytype_in(cstring)
 RETURNS mytype AS 'int4in'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
NOTICE:  return type mytype is only a shell
CREATE FUNCTION mytype_out(mytype)
 RETURNS cstring AS 'int4out'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
NOTICE:  argument type mytype is only a shell
LINE 1: CREATE FUNCTION mytype_out(mytype)
                                   ^
CREATE TYPE mytype (
 LIKE = int4,
 INPUT = mytype_in,
 OUTPUT = mytype_out
);
CREATE FUNCTION mytype_eq(mytype, mytype)
 RETURNS bool AS 'int4eq'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE FUNCTION mytype_lt(mytype, mytype)
 RETURNS bool AS 'int4lt'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE FUNCTION mytype_cmp(mytype, mytype)
 RETURNS integer AS 'btint4cmp'
 LANGUAGE INTERNAL STRICT IMMUTABLE;
CREATE OPERATOR = (
 leftarg = mytype, rightarg = mytype,
 procedure = mytype_eq);
CREATE OPERATOR < (
 leftarg = mytype, rightarg = mytype,
 procedure = mytype_lt);
CREATE OPERATOR CLASS mytype_ops
 DEFAULT FOR TYPE mytype USING btree AS
 OPERATOR        1       <,
 OPERATOR        3       = ,
 FUNCTION		1		mytype_cmp(mytype,mytype);
CREATE TABLE t_mytype (x mytype);
SELECT pgivm.create_immv('mv_mytype',
 'SELECT * FROM t_mytype');
NOTICE:  could not create an index on immv "mv_mytype" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           0
(1 row)

INSERT INTO t_mytype VALUES ('1'::mytype);
SELECT * FROM mv_mytype;
 x 
---
 1
(1 row)

ROLLBACK;
-- outer join is not supported
SELECT pgivm.create_immv('mv(a,b)',
    'SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i');
ERROR:  OUTER JOIN is not supported on incrementally maintainable materialized view
-- contain system column
SELECT pgivm.create_immv('mv_ivm01', 'SELECT i,j,xmin FROM mv_base_a');
ERROR:  system column is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm02', 'SELECT i,j FROM mv_base_a WHERE xmin = ''610''');
ERROR:  system column is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm03', 'SELECT i,j,xmin::text AS x_min FROM mv_base_a');
ERROR:  system column is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm04', 'SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a');
ERROR:  system column is not supported on incrementally maintainable materialized view
-- contain ORDER BY
SELECT pgivm.create_immv('mv_ivm07', 'SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k');
ERROR:  ORDER BY clause is not supported on incrementally maintainable materialized view
-- contain HAVING
SELECT pgivm.create_immv('mv_ivm08', 'SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5');
ERROR:  HAVING clause is not supported on incrementally maintainable materialized view
-- contain GROUP BY without aggregate
SELECT pgivm.create_immv('mv_ivm08', 'SELECT i,j FROM mv_base_a GROUP BY i,j');
ERROR:  GROUP BY clause without aggregate is not supported on incrementally maintainable materialized view
-- contain view or materialized view
CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;
CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;
SELECT pgivm.create_immv('mv_ivm07', 'SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i');
ERROR:  VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm08', 'SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i');
ERROR:  VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
-- contain mutable functions
SELECT pgivm.create_immv('mv_ivm12', 'SELECT i,j FROM mv_base_a WHERE i = random()::int');
ERROR:  mutable function is not supported on incrementally maintainable materialized view
HINT:  functions must be marked IMMUTABLE
-- LIMIT/OFFSET is not supported
SELECT pgivm.create_immv('mv_ivm13', 'SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5');
ERROR:  LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view
-- DISTINCT ON is not supported
SELECT pgivm.create_immv('mv_ivm14', 'SELECT DISTINCT ON(i) i, j FROM mv_base_a');
ERROR:  DISTINCT ON is not supported on incrementally maintainable materialized view
-- TABLESAMPLE clause is not supported
SELECT pgivm.create_immv('mv_ivm15', 'SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50)');
ERROR:  TABLESAMPLE clause is not supported on incrementally maintainable materialized view
-- window functions are not supported
SELECT pgivm.create_immv('mv_ivm16', 'SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a');
ERROR:  window functions are not supported on incrementally maintainable materialized view
-- aggregate function with some options is not supported
SELECT pgivm.create_immv('mv_ivm17', 'SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a');
ERROR:  aggregate function with FILTER clause is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm18', 'SELECT COUNT(DISTINCT i)  FROM mv_base_a');
ERROR:  aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm19', 'SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a');
ERROR:  aggregate function with ORDER clause is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm20', 'SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),())');
ERROR:  GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view
-- inheritance parent is not supported
BEGIN;
CREATE TABLE parent (i int, v int);
CREATE TABLE child_a(options text) INHERITS(parent);
SELECT pgivm.create_immv('mv_ivm21', 'SELECT * FROM parent');
ERROR:  inheritance parent is not supported on incrementally maintainable materialized view
ROLLBACK;
-- UNION statement is not supported
SELECT pgivm.create_immv('mv_ivm22', 'SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b');
ERROR:  UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view
-- DISTINCT clause in nested query are not supported
SELECT pgivm.create_immv('mv_ivm23', 'SELECT * FROM (SELECT DISTINCT i,j FROM mv_base_a) AS tmp');;
ERROR:  DISTINCT clause in nested query are not supported on incrementally maintainable materialized view
-- empty target list is not allowed with IVM
SELECT pgivm.create_immv('mv_ivm25', 'SELECT FROM mv_base_a');
ERROR:  empty target list is not supported on incrementally maintainable materialized view
-- FOR UPDATE/SHARE is not supported
SELECT pgivm.create_immv('mv_ivm26', 'SELECT i,j FROM mv_base_a FOR UPDATE');
ERROR:  FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm27', 'SELECT * FROM (SELECT i,j FROM mv_base_a FOR UPDATE) AS tmp;');
ERROR:  FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view
-- tartget list cannot contain ivm column that start with '__ivm'
SELECT pgivm.create_immv('mv_ivm28', 'SELECT i AS "__ivm_count__" FROM mv_base_a');
ERROR:  column name __ivm_count__ is not supported on incrementally maintainable materialized view
-- expressions specified in GROUP BY must appear in the target list.
SELECT pgivm.create_immv('mv_ivm29', 'SELECT COUNT(i) FROM mv_base_a GROUP BY i;');
ERROR:  GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view
-- experssions containing an aggregate is not supported
SELECT pgivm.create_immv('mv_ivm30', 'SELECT sum(i)*0.5 FROM mv_base_a');
ERROR:  expression containing an aggregate in it is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm31', 'SELECT sum(i)/sum(j) FROM mv_base_a');
ERROR:  expression containing an aggregate in it is not supported on incrementally maintainable materialized view
-- VALUES is not supported
SELECT pgivm.create_immv('mv_ivm_only_values1', 'values(1)');
ERROR:  VALUES is not supported on incrementally maintainable materialized view
SELECT pgivm.create_immv('mv_ivm_only_values2',  'SELECT * FROM (values(1)) AS tmp');
ERROR:  VALUES is not supported on incrementally maintainable materialized view
-- views containing base tables with Row Level Security
DROP USER IF EXISTS regress_ivm_admin;
NOTICE:  role "regress_ivm_admin" does not exist, skipping
DROP USER IF EXISTS regress_ivm_user;
NOTICE:  role "regress_ivm_user" does not exist, skipping
CREATE USER regress_ivm_admin;
CREATE USER regress_ivm_user;
--- create a table with RLS
SET SESSION AUTHORIZATION regress_ivm_admin;
CREATE TABLE rls_tbl(id int, data text, owner name);
INSERT INTO rls_tbl VALUES
  (1,'foo','regress_ivm_user'),
  (2,'bar','postgres');
CREATE TABLE num_tbl(id int, num text);
INSERT INTO num_tbl VALUES
  (1,'one'),
  (2,'two'),
  (3,'three'),
  (4,'four'),
  (5,'five'),
  (6,'six');
--- Users can access only their own rows
CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user);
ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
GRANT ALL on rls_tbl TO PUBLIC;
GRANT ALL on num_tbl TO PUBLIC;
--- create a view owned by regress_ivm_user
SET SESSION AUTHORIZATION regress_ivm_user;
SELECT pgivm.create_immv('ivm_rls', 'SELECT * FROM rls_tbl');
NOTICE:  could not create an index on immv "ivm_rls" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           1
(1 row)

SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
 id | data |      owner       
----+------+------------------
  1 | foo  | regress_ivm_user
(1 row)

RESET SESSION AUTHORIZATION;
--- inserts rows owned by different users
INSERT INTO rls_tbl VALUES
  (3,'baz','regress_ivm_user'),
  (4,'qux','postgres');
SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
 id | data |      owner       
----+------+------------------
  1 | foo  | regress_ivm_user
  3 | baz  | regress_ivm_user
(2 rows)

--- combination of diffent kinds of commands
WITH
 i AS (INSERT INTO rls_tbl VALUES(5,'quux','postgres'), (6,'corge','regress_ivm_user')),
 u AS (UPDATE rls_tbl SET owner = 'postgres' WHERE id = 1),
 u2 AS (UPDATE rls_tbl SET owner = 'regress_ivm_user' WHERE id = 2)
SELECT;
--
(1 row)

SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
 id | data  |      owner       
----+-------+------------------
  2 | bar   | regress_ivm_user
  3 | baz   | regress_ivm_user
  6 | corge | regress_ivm_user
(3 rows)

---
SET SESSION AUTHORIZATION regress_ivm_user;
SELECT pgivm.create_immv('ivm_rls2', 'SELECT * FROM rls_tbl JOIN num_tbl USING(id)');
NOTICE:  could not create an index on immv "ivm_rls2" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           3
(1 row)

RESET SESSION AUTHORIZATION;
WITH
 x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)),
 y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4))
SELECT;
--
(1 row)

SELECT * FROM ivm_rls2 ORDER BY 1,2,3;
 id | data  |      owner       |   num   
----+-------+------------------+---------
  2 | bar   | regress_ivm_user | two
  3 | baz_2 | regress_ivm_user | three_2
  6 | corge | regress_ivm_user | six
(3 rows)

DROP TABLE rls_tbl CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table ivm_rls
drop cascades to table ivm_rls2
DROP TABLE num_tbl CASCADE;
DROP USER regress_ivm_user;
DROP USER regress_ivm_admin;
-- automatic index creation
BEGIN;
CREATE TABLE base_a (i int primary key, j int);
CREATE TABLE base_b (i int primary key, j int);
--- group by: create an index
SELECT pgivm.create_immv('mv_idx1', 'SELECT i, sum(j) FROM base_a GROUP BY i');
NOTICE:  created index "mv_idx1_index" on immv "mv_idx1"
 create_immv 
-------------
           0
(1 row)

--- distinct: create an index
SELECT pgivm.create_immv('mv_idx2', 'SELECT DISTINCT j FROM base_a');
NOTICE:  created index "mv_idx2_index" on immv "mv_idx2"
 create_immv 
-------------
           0
(1 row)

--- with all pkey columns: create an index
SELECT pgivm.create_immv('mv_idx3(i_a, i_b)', 'SELECT a.i, b.i FROM base_a a, base_b b');
NOTICE:  created index "mv_idx3_index" on immv "mv_idx3"
 create_immv 
-------------
           0
(1 row)

--- missing some pkey columns: no index
SELECT pgivm.create_immv('mv_idx4', 'SELECT j FROM base_a');
NOTICE:  could not create an index on immv "mv_idx4" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           0
(1 row)

SELECT pgivm.create_immv('mv_idx5', 'SELECT a.i, b.j FROM base_a a, base_b b');
NOTICE:  could not create an index on immv "mv_idx5" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           0
(1 row)

--- subqueries: create an index
SELECT pgivm.create_immv('mv_idx6(i_a, i_b)', 'SELECT a.i, b.i FROM (SELECT * FROM base_a) a, (SELECT * FROM base_b) b');
NOTICE:  created index "mv_idx6_index" on immv "mv_idx6"
 create_immv 
-------------
           0
(1 row)

--- with set-returning function: no index
SELECT pgivm.create_immv('mv_idx7', 'SELECT i FROM base_a, generate_series(1,10)');
NOTICE:  could not create an index on immv "mv_idx7" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           0
(1 row)

ROLLBACK;
-- type that doesn't have default operator class for access method btree
BEGIN;
CREATE TABLE table_json (j json);
SELECT pgivm.create_immv('mv_json', 'SELECT * from table_json');
ERROR:  data type json has no default operator class for access method "btree"
ROLLBACK;
-- prevent IMMV chanages
INSERT INTO mv_ivm_1 VALUES(1,1,1);
ERROR:  cannot change materialized view "mv_ivm_1"
UPDATE  mv_ivm_1 SET k = 1 WHERE i = 1;
ERROR:  cannot change materialized view "mv_ivm_1"
DELETE FROM mv_ivm_1;
ERROR:  cannot change materialized view "mv_ivm_1"
TRUNCATE mv_ivm_1;
ERROR:  cannot change materialized view "mv_ivm_1"
-- get_immv_def function
SELECT immvrelid, pgivm.get_immv_def(immvrelid) FROM pgivm.pg_ivm_immv ORDER BY 1;
 immvrelid |           get_immv_def           
-----------+----------------------------------
 mv_ivm_1  |  SELECT a.i,                    +
           |     a.j,                        +
           |     b.k                         +
           |    FROM (mv_base_a a            +
           |      JOIN mv_base_b b USING (i))
(1 row)

-- mv_base_b is not immv
SELECT 'mv_base_b'::regclass, pgivm.get_immv_def('mv_base_b');
 regclass  | get_immv_def 
-----------+--------------
 mv_base_b | 
(1 row)

DROP TABLE mv_base_b CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table mv_ivm_1
drop cascades to view b_view
drop cascades to materialized view b_mview
DROP TABLE mv_base_a CASCADE;