目录

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()" 的评论、建议或更正 此处。