--
-- Tests for functions providing information about partitions
--
SELECT * FROM pg_partition_tree(NULL);
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
(0 rows)

SELECT * FROM pg_partition_tree(0);
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
       |             |        |      
(1 row)

SELECT pg_partition_root(NULL);
 pg_partition_root 
-------------------
 
(1 row)

SELECT pg_partition_root(0);
 pg_partition_root 
-------------------
 
(1 row)

-- Test table partition trees
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
CREATE TABLE ptif_test0 PARTITION OF ptif_test
  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
CREATE TABLE ptif_test01 PARTITION OF ptif_test0 FOR VALUES IN (1);
CREATE TABLE ptif_test1 PARTITION OF ptif_test
  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
CREATE TABLE ptif_test11 PARTITION OF ptif_test1 FOR VALUES IN (1);
CREATE TABLE ptif_test2 PARTITION OF ptif_test
  FOR VALUES FROM (100) TO (maxvalue);
-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index;
CREATE INDEX ptif_test01_index ON ptif_test01 (a);
ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index;
CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index;
CREATE INDEX ptif_test11_index ON ptif_test11 (a);
ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index;
CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
-- List all tables members of the tree
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test');
    relid    | parentrelid | level | isleaf 
-------------+-------------+-------+--------
 ptif_test   |             |     0 | f
 ptif_test0  | ptif_test   |     1 | f
 ptif_test1  | ptif_test   |     1 | f
 ptif_test2  | ptif_test   |     1 | t
 ptif_test01 | ptif_test0  |     2 | t
 ptif_test11 | ptif_test1  |     2 | t
(6 rows)

-- List tables from an intermediate level
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test0') p
  JOIN pg_class c ON (p.relid = c.oid);
    relid    | parentrelid | level | isleaf 
-------------+-------------+-------+--------
 ptif_test0  | ptif_test   |     0 | f
 ptif_test01 | ptif_test0  |     1 | t
(2 rows)

-- List from leaf table
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test01') p
  JOIN pg_class c ON (p.relid = c.oid);
    relid    | parentrelid | level | isleaf 
-------------+-------------+-------+--------
 ptif_test01 | ptif_test0  |     0 | t
(1 row)

-- List all members using pg_partition_root with leaf table reference
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
  JOIN pg_class c ON (p.relid = c.oid);
    relid    | parentrelid | level | isleaf 
-------------+-------------+-------+--------
 ptif_test   |             |     0 | f
 ptif_test0  | ptif_test   |     1 | f
 ptif_test1  | ptif_test   |     1 | f
 ptif_test2  | ptif_test   |     1 | t
 ptif_test01 | ptif_test0  |     2 | t
 ptif_test11 | ptif_test1  |     2 | t
(6 rows)

-- List all indexes members of the tree
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test_index');
       relid       |   parentrelid    | level | isleaf 
-------------------+------------------+-------+--------
 ptif_test_index   |                  |     0 | f
 ptif_test0_index  | ptif_test_index  |     1 | f
 ptif_test1_index  | ptif_test_index  |     1 | f
 ptif_test2_index  | ptif_test_index  |     1 | t
 ptif_test01_index | ptif_test0_index |     2 | t
 ptif_test11_index | ptif_test1_index |     2 | t
(6 rows)

-- List indexes from an intermediate level
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test0_index') p
  JOIN pg_class c ON (p.relid = c.oid);
       relid       |   parentrelid    | level | isleaf 
-------------------+------------------+-------+--------
 ptif_test0_index  | ptif_test_index  |     0 | f
 ptif_test01_index | ptif_test0_index |     1 | t
(2 rows)

-- List from leaf index
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_test01_index') p
  JOIN pg_class c ON (p.relid = c.oid);
       relid       |   parentrelid    | level | isleaf 
-------------------+------------------+-------+--------
 ptif_test01_index | ptif_test0_index |     0 | t
(1 row)

-- List all members using pg_partition_root with leaf index reference
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
  JOIN pg_class c ON (p.relid = c.oid);
       relid       |   parentrelid    | level | isleaf 
-------------------+------------------+-------+--------
 ptif_test_index   |                  |     0 | f
 ptif_test0_index  | ptif_test_index  |     1 | f
 ptif_test1_index  | ptif_test_index  |     1 | f
 ptif_test2_index  | ptif_test_index  |     1 | t
 ptif_test01_index | ptif_test0_index |     2 | t
 ptif_test11_index | ptif_test1_index |     2 | t
(6 rows)

DROP TABLE ptif_test;
-- Table that is not part of any partition tree is the only member listed.
CREATE TABLE ptif_normal_table(a int);
SELECT relid, parentrelid, level, isleaf
  FROM pg_partition_tree('ptif_normal_table');
       relid       | parentrelid | level | isleaf 
-------------------+-------------+-------+--------
 ptif_normal_table |             |     0 | t
(1 row)

SELECT pg_partition_root('ptif_normal_table');
 pg_partition_root 
-------------------
 ptif_normal_table
(1 row)

DROP TABLE ptif_normal_table;
-- Various partitioning-related functions return NULL if passed relations
-- of types that cannot be part of a partition tree; for example, views,
-- materialized views, etc.
CREATE VIEW ptif_test_view AS SELECT 1;
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
SELECT * FROM pg_partition_tree('ptif_test_view');
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
       |             |        |      
(1 row)

SELECT * FROM pg_partition_tree('ptif_test_matview');
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
       |             |        |      
(1 row)

SELECT pg_partition_root('ptif_test_view');
 pg_partition_root 
-------------------
 
(1 row)

SELECT pg_partition_root('ptif_test_matview');
 pg_partition_root 
-------------------
 
(1 row)

DROP VIEW ptif_test_view;
DROP MATERIALIZED VIEW ptif_test_matview;