GBase 8c系统表信息函数（三）

1. pg_get_function_result(func_oid)

描述：获取函数的RETURNS子句。

返回类型：text

备注：pg_get_function_result为函数返回适当的RETURNS子句。

1. pg_get_indexdef(index_oid)

描述：获取索引的CREATE INDEX命令。

返回类型：text

示例：

 gbase=# select * from pg_get_indexdef(16416);

                             pg_get_indexdef                             

-------------------------------------------------------------------------

 CREATE INDEX test3_b_idx ON test3 USING btree (b) TABLESPACE pg_default

(1 row)

1. pg_get_indexdef(index_oid, dump_schema_only)

描述：获取索引的CREATE INDEX命令，仅用于dump场景。对于包含local索引的间隔分区表，当dump_schema_only为true时，返回的创建索引语句中不包含自动创建的分区的local索引信息；当dump_schema_only为false时，返回的创建索引语句中包含自动创建的分区的local索引信息。对于非间隔分区表或者不包含local索引的间隔分区分区表，dump_schema_only参数取值不影响函数返回结果。

返回类型：text

示例：

gbase=# CREATE TABLE sales

gbase-# (prod_id NUMBER(6),

gbase(#  cust_id NUMBER,

gbase(#  time_id DATE,

gbase (#  channel_id CHAR(1),

gbase (#  promo_id NUMBER(6),

gbase (#  quantity_sold NUMBER(3),

gbase (#  amount_sold NUMBER(10,2)

gbase (# )

gbase-# PARTITION BY RANGE( time_id) INTERVAL('1 day')

gbase -# (

gbase (# partition p1 VALUES LESS THAN ('2019-02-01 00:00:00'),

gbase (# partition p2 VALUES LESS THAN ('2019-02-02 00:00:00')

gbase (# );

CREATE TABLE

gbase=# create index index_sales on sales(prod_id) local (PARTITION idx_p1 ,PARTITION idx_p2);

CREATE INDEX

-- 插入数据没有匹配的分区，新创建一个分区，并将数据插入该分区

gbase=# INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'a', 1, 1, 1);

INSERT 0 1

gbase=# select oid from pg_class where relname = 'index_sales';

  oid

-------

 16636

(1 row)

gbase=# select * from pg_get_indexdef(16636, true);                                                                pg_get_indexdef

------------------------------------------------------------------------------------------------------------------------------------------------------

 CREATE INDEX index_sales ON sales USING ubtree (prod_id) LOCAL(PARTITION idx_p1, PARTITION idx_p2)  WITH (storage_type=USTORE) TABLESPACE pg_default

(1 row)

gbase=# select * from pg_get_indexdef(16636, false);

                pg_get_indexdef

------------------------------------------------------------------

------------------------------------------------------------------

------------------------------------------------

 CREATE INDEX index_sales ON sales USING ubtree (prod_id) LOCAL(PA

RTITION idx_p1, PARTITION idx_p2, PARTITION sys_p1_prod_id_idx)  W

ITH (storage_type=USTORE) TABLESPACE pg_default

(1 row)