目录

pg_partition_tree()

列出分区关系组成部分分区的函数

pg_partition_tree() 是一个用于列出分区表或索引的组成部分分区及其关系的系统函数。

pg_partition_tree() 添加于 PostgreSQL 12。

用法

pg_partition_tree ( regclass ) → 
        setof record ( relid regclass, parentrelid regclass, isleaf boolean, level integer )

如果指定的关系不是分区表或索引，则不返回任何行。

变更历史

示例

使用以下示例分区表

CREATE TABLE partition_test (
  id  INT NOT NULL PRIMARY KEY,
  val TEXT
) PARTITION BY RANGE (id);

CREATE TABLE partition_test_default
  PARTITION OF partition_test
  DEFAULT;

CREATE TABLE partition_test_1_10 (
  id  INT NOT NULL PRIMARY KEY CHECK (id BETWEEN 1 AND 10),
  val TEXT
) PARTITION BY RANGE (id);

CREATE TABLE partition_test_11_20 (
  id  INT NOT NULL PRIMARY KEY CHECK (id BETWEEN 11 AND 20),
  val TEXT
);

ALTER TABLE partition_test
  ATTACH PARTITION partition_test_1_10 FOR VALUES FROM (1) TO (11);
ALTER TABLE partition_test
  ATTACH PARTITION partition_test_11_20 FOR VALUES FROM (11) TO (21);

CREATE TABLE partition_test_1_5 (
  id  INT NOT NULL PRIMARY KEY,
  val TEXT,
  CONSTRAINT partition_test_1_10_id_check CHECK (id BETWEEN 1 AND 10)
);

CREATE TABLE partition_test_6_10 (
  id  INT NOT NULL PRIMARY KEY,
  val TEXT,
  CONSTRAINT partition_test_1_10_id_check CHECK (id BETWEEN 1 AND 10)
);

ALTER TABLE partition_test_1_10
  ATTACH PARTITION partition_test_1_5 FOR VALUES FROM (1) TO (6);
ALTER TABLE partition_test_1_10
  ATTACH PARTITION partition_test_6_10 FOR VALUES FROM (6) TO (11);

pg_partition_tree() 产生以下输出

postgres=# SELECT * FROM pg_partition_tree('partition_test');
         relid          |     parentrelid     | isleaf | level 
------------------------+---------------------+--------+-------
 partition_test         |                     | f      |     0
 partition_test_default | partition_test      | t      |     1
 partition_test_1_10    | partition_test      | f      |     1
 partition_test_11_20   | partition_test      | t      |     1
 partition_test_1_5     | partition_test_1_10 | t      |     2
 partition_test_6_10    | partition_test_1_10 | t      |     2
(6 rows)

在不属于分区层级关系的关系上执行

postgres=# SELECT * FROM pg_partition_tree('pg_class');
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
(0 rows)

使用不存在的关系名称执行 pg_partition_tree()

postgres=# SELECT * FROM pg_partition_tree('foo');
ERROR:  relation "foo" does not exist
LINE 1: SELECT * FROM pg_partition_tree('foo');

分类

分区, 系统函数

另请参阅

pg_partition_root(), pg_partition_ancestors()

反馈

提交有关“pg_partition_tree()”的任何评论、建议或更正 在此处。