目录
概述
介绍一些常用的获取磐维数据库对象DDL语句的方法。
一、表的ddl语句
1、新建测试表
CREATE TABLE sales
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY RANGE( time_id) INTERVAL('1 day')
(
partition p1 VALUES LESS THAN ('2019-02-01 00:00:00'),
partition p2 VALUES LESS THAN ('2019-02-02 00:00:00')
);
create index on sales(prod_id) local ;
2、直接查询pg_get_tabledef函数
dbtest=# select oid from pg_class where relname ='sales';
oid
--------
155519
(1 row)
dbtest=# select pg_get_tabledef(155519);
pg_get_tabledef
-------------------------------------------------------------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE sales ( +
prod_id numeric(6,0), +
cust_id numeric, +
time_id timestamp(0) without time zone, +
channel_id character(1), +
promo_id numeric(6,0), +
quantity_sold numeric(3,0), +
amount_sold numeric(10,2) +
) +
WITH (orientation=row, compression=no) +
PARTITION BY RANGE (time_id) +
INTERVAL ('1 day') +
( +
PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00') TABLESPACE pg_default, +
PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00') TABLESPACE pg_default +
) +
ENABLE ROW MOVEMENT; +
CREATE INDEX sales_prod_id_idx ON sales USING btree (prod_id) LOCAL(PARTITION p1_prod_id_idx, PARTITION p2_prod_id_idx) TABLESPACE pg_default;
(1 row)
dbtest=#
3、第二种方法，直接查询pg_class
btest=# select pg_get_tabledef(oid) from pg_class where relname ='sales';
pg_get_tabledef
-------------------------------------------------------------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE sales ( +
prod_id numeric(6,0), +
cust_id numeric, +
time_id timestamp(0) without time zone, +
channel_id character(1), +
promo_id numeric(6,0), +
quantity_sold numeric(3,0), +
amount_sold numeric(10,2) +
) +
WITH (orientation=row, compression=no) +
PARTITION BY RANGE (time_id) +
INTERVAL ('1 day') +
( +
PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00') TABLESPACE pg_default, +
PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00') TABLESPACE pg_default +
) +
ENABLE ROW MOVEMENT; +
CREATE INDEX sales_prod_id_idx ON sales USING btree (prod_id) LOCAL(PARTITION p1_prod_id_idx, PARTITION p2_prod_id_idx) TABLESPACE pg_default;
(1 row)
dbtest=#
二、 索引的ddl语句
1、直接查询pg_get_indexdef函数
dbtest=# CREATE TABLE staff(
dbtest(# ID INT NOT NULL,
dbtest(# NAME char(8) NOT NULL,
dbtest(# AGE INT ,
dbtest(# ADDRESS CHAR(50),
dbtest(# SALARY REAL
dbtest(# );
CREATE TABLE
dbtest=#
dbtest=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+--------+-------+-------+----------------------------------
public | staff | table | omm | {orientation=row,compression=no}
public | th_hhd | table | omm | {orientation=row,compression=no}
(2 rows)
dbtest=# \d staff
Table "public.staff"
Column | Type | Modifiers
---------+---------------+-----------
id | integer | not null
name | character(8) | not null
age | integer |
address | character(50) |
salary | real |
dbtest=# create index on staff(id);
CREATE INDEX
dbtest=# select oid,relname from pg_class where relname='staff_id_idx';
oid | relname
--------+--------------
155517 | staff_id_idx
(1 row)
dbtest=#
MogDB=# CREATE TABLE sales
mogdb-# (prod_id NUMBER(6),
MogDB(# cust_id NUMBER,
MogDB(# time_id DATE,
MogDB(# channel_id CHAR(1),
MogDB(# promo_id NUMBER(6),
MogDB(# quantity_sold NUMBER(3),
MogDB(# amount_sold NUMBER(10,2)
MogDB(# )
PARTITION BY RANGE( time_id) INTERVAL('1 day')
mogdb-# mogdb-# (
MogDB(# partition p1 VALUES LESS THAN ('2019-02-01 00:00:00'),
MogDB(# partition p2 VALUES LESS THAN ('2019-02-02 00:00:00')
MogDB(# );
CREATE TABLE
MogDB=# create index index_sales on sales(prod_id) local (PARTITION idx_p1 ,PARTITION idx_p2);
CREATE INDEX
MogDB=# -- 插入数据没有匹配的分区，新创建一个分区，并将数据插入该分区
MogDB=# INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'a', 1, 1, 1);
INSERT 0 1
MogDB=# select oid from pg_class where relname = 'index_sales';
oid
-------
24632
(1 row)
MogDB=# select * from pg_get_indexdef(24632, true);
pg_get_indexdef
--------------------------------------------------------------------------------------------------------------------------
CREATE INDEX index_sales ON sales USING btree (prod_id) LOCAL(PARTITION idx_p1, PARTITION idx_p2) TABLESPACE pg_default
(1 row)
MogDB=# select * from pg_get_indexdef(24632, false);
pg_get_indexdef
------------------------------------------------------------------------------------------------------------------------------------
--------------------
CREATE INDEX index_sales ON sales USING btree (prod_id) LOCAL(PARTITION idx_p1, PARTITION idx_p2, PARTITION sys_p1_prod_id_idx) TA
BLESPACE pg_default
(1 row
2、第二种方法，直接查询pg_class
dbtest=# select pg_get_indexdef(a.oid) from pg_class a where a.relname='staff_id_idx' ;
pg_get_indexdef
---------------------------------------------------------------------------
CREATE INDEX staff_id_idx ON staff USING btree (id) TABLESPACE pg_default
(1 row)
dbtest=#
三、获取函数的ddl语句
1、直接查询pg_get_functiondef函数
dbtest=# select oid from pg_proc where proname='ap_panwei_drop_partition';
oid
--------
155518
(1 row)
dbtest=# select pg_get_functiondef(155518);
pg_get_functiondef
-------------------------------------------------------------------------------------------------------------------------------------------------------------
(1,"CREATE OR REPLACE PROCEDURE public.ap_panwei_drop_partition()
AS DECLARE
begin
for cc in (select a.relname
from pg_partition a join pg_class b on (a.parentid = b.oid)
where a.parttype = 'p' and b.relname = 'aopen_api_cdrrsp_0' and substr(a.relname,6) < to_char(date_trunc('day',current_date) - interval'60 day','yyyymmdd')
order by 1) loop
execute immediate 'alter table AOPEN_API_CDRREQ_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_NEW_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_NEW_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_NEW_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_NEW_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_1 drop partition '||cc.relname;
end loop;
end;
/
")
2、第二种方法，直接查询pg_class
select pg_get_functiondef(oid) from pg_proc where proname='ap_panwei_drop_partition';
------------------------------------------------------------------------------------------------------------------------------------------------------------
(1,"CREATE OR REPLACE PROCEDURE public.ap_panwei_drop_partition()
AS DECLARE
begin
for cc in (select a.relname
from pg_partition a join pg_class b on (a.parentid = b.oid)
where a.parttype = 'p' and b.relname = 'aopen_api_cdrrsp_0' and substr(a.relname,6) < to_char(date_trunc('day',current_date) - interval'60 day','yyyymmdd')
order by 1) loop
execute immediate 'alter table AOPEN_API_CDRREQ_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_NEW_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRREQ_NEW_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_NEW_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRRSP_NEW_1 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_0 drop partition '||cc.relname;
execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_1 drop partition '||cc.relname;
end loop;
end;
/
四、获取视图的ddl语句
1、直接查询pg_views视图
dbtest=# select * from pg_views where viewname='v_th';
schemaname | viewname | viewowner | definition
------------+----------+-----------+---------------------------------------
public | v_th | omm | SELECT count(*) AS count FROM th_hhd;
2、第二种方法，直接查询pg_class
dbtest=# select pg_get_viewdef(a.oid) from pg_class a, pg_views b where a.relname=b.viewname and b.viewname='v_th' ;
pg_get_viewdef
---------------------------------------
SELECT count(*) AS count FROM th_hhd;
(1 row)
dbtest=#
3、第三种方法，直接查询pg_get_viewdef
dbtest=# select oid from pg_class where relname='v_th';
oid
--------
155548
(1 row)
dbtest=# select * from pg_get_viewdef(155548);
pg_get_viewdef
---------------------------------------
SELECT count(*) AS count FROM th_hhd;
(1 row)
五、获取RULE 的ddl语句
dbtest=# select * from PG_RULES;
schemaname | tablename | rulename | definition
------------+-------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------
pg_catalog | pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
pg_catalog | pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
public | t1 | t1_ins | CREATE RULE t1_ins AS ON INSERT TO t1 DO INSTEAD INSERT INTO t2 (id) VALUES (new.id);
(3 rows)
六、 获取 物化视图 的ddl语句
1、直接查询pg_get_viewdef函数
dbtest=# CREATE MATERIALIZED VIEW mv_t3 AS select count(*) from t3
dbtest-# ;
CREATE MATERIALIZED VIEW
dbtest=# select * from pg_get_viewdef('mv_t3');
pg_get_viewdef
-----------------------------------
SELECT count(*) AS count FROM t3;
(1 row)
dbtest=#
七、获取 触发器的ddl语句
dbtest=# select pg_get_triggerdef(oid) from pg_trigger where tgname='warehouse_log';
pg_get_triggerdef
----------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER warehouse_log AFTER INSERT OR DELETE OR UPDATE ON warehouse FOR EACH ROW EXECUTE PROCEDURE record_warehouse_log()
(1 row)
dbtest=#