一、获取表的ddl语句
1、查询系统表 information_schema.columns
请注意替换sql中的schemaname和tablename。
查询1 ：表结构，也可以使用 \d table_name 查看
caitdb=# SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'company';
column_name | data_type
-------------+-----------
id | integer
name | text
age | integer
address | character
salary | real
join_date | date
查询2 : 表的ddl语句
caitdb=# SELECT 'CREATE TABLE ' || table_schema || '.' || table_name || ' (' || string_agg(column_name || ' ' || column_type, ', ') || ');'
FROM (
SELECT
table_schema,
table_name,
column_name,
CASE
WHEN data_type = 'character varying' THEN 'VARCHAR(' || character_maximum_length || ')'
WHEN data_type = 'character' THEN 'CHAR(' || character_maximum_length || ')'
WHEN data_type = 'numeric' THEN 'NUMERIC(' || numeric_precision || ',' || numeric_scale || ')'
ELSE data_type
END AS column_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'emp_president'
) AS column_definitions
GROUP BY table_schema, table_name;2、查询 pg_get_tabledef()函数
caitdb=# select pg_get_tabledef('bt_to_boss'::regclass);
PG_GET_TABLEDEF
--------------------------------------------------------
SET search_path = db2instl; +
CREATE TABLE bt_to_boss ( +
id varchar(32) NOT NULL, +
datetime varchar(50) NOT NULL, +
flag varchar(50) +
) +
WITH (orientation=row, compression=no, fillfactor=80);
(1 row)3、如果想要查询所有表的DDL，则使用pg_dump进行导出
postgres@Danson:~> pg_dump -h localhost -p 5432 -n public -U postgres --schema-only -f schema_public.ddl caitdb
# --schema-only 表示只导出表结构，也可以写成 -s # --data-only 表示只导出表数据，也可以写成 -a # -n 指定schema # -U 指定用户 # -f 导出到指定文件
二、获取索引的ddl语句
1、通过 \di 列表所有索引
caitdb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------------+-------+----------+------------------
public | company_pkey | index | postgres | company
public | pgbench_accounts_pkey | index | postgres | pgbench_accounts
public | pgbench_branches_pkey | index | postgres | pgbench_branches
public | pgbench_tellers_pkey | index | postgres | pgbench_tellers
2、通过函数 pg_get_indexdef() 查看索引定义
caitdb=# select pg_get_indexdef('company_pkey'::regclass);
pg_get_indexdef
---------------------------------------------------------------------
CREATE UNIQUE INDEX company_pkey ON public.company USING btree (id)
(1 row)3、查询 pg_indexes 视图
caitdb=# select indexdef from pg_indexes where indexname='company_pkey';
indexdef
---------------------------------------------------------------------
CREATE UNIQUE INDEX company_pkey ON public.company USING btree (id)
(1 row)
4、获取表的所有索引及索引定义
caitdb=# SELECT * FROM pg_indexes WHERE tablename = 'company';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+--------------+------------+------------------
public | company | company_pkey | | CREATE UNIQUE INDEX company_pkey ON public.company USING btree (id)
5、获取所有表的索引名称及其索引定义
postgres=# \x
postgres=# select * from pg_indexes;
-[ RECORD 1 ]----------------------------------------------------------
schemaname | pg_catalog
tablename | pg_proc
indexname | pg_proc_oid_index
tablespace |
indexdef | CREATE UNIQUE INDEX pg_proc_oid_index ON pg_catalog.pg_proc USING btree (oid)
-[ RECORD 2 ]----------------------------------------------------------
schemaname | pg_catalog
tablename | pg_proc
indexname | pg_proc_proname_args_nsp_index
tablespace |
indexdef | CREATE UNIQUE INDEX pg_proc_proname_args_nsp_index ON pg_catalog.pg_proc USING btree (proname, proargtypes, pronamespace)
三、获取函数的ddl语句
1、pg_get_functiondef()函数
# 创建测试function
caitdb=# CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $$
BEGIN
RAISE NOTICE 'This is a test function.';
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
# 查询function定义
caitdb=# select pg_get_functiondef('test_function'::regproc);
pg_get_functiondef
\---------------------------------------------------
CREATE OR REPLACE FUNCTION public.test_function()+
RETURNS void +
LANGUAGE plpgsql +
AS $function$ +
BEGIN +
RAISE NOTICE 'This is a test function.'; +
END; +
$function$ +
(1 row)
四、获取视图的ddl语句
1、创建测试视图
caitdb=# create view view_celer as select c.id,c.name,e.emp,e,sal from company c,emp_clerk e;
CREATE VIEW
caitdb=#
2、pg_get_viewdef()函数
caitdb=# select pg_get_viewdef('view_celer');
pg_get_viewdef
--------------------------
SELECT c.id, +
c.name, +
e.emp, +
e.*::emp_clerk AS e,+
e.sal +
FROM company c, +
emp_clerk e;
(1 row)
caitdb=# 3、查询pg_views视图
caitdb=# select definition from pg_views where viewname='view_celer';
definition
--------------------------
SELECT c.id, +
c.name, +
e.emp, +
e.*::emp_clerk AS e,+
e.sal +
FROM company c, +
emp_clerk e;
(1 row)
五、获取RULE 的ddl语句
1、创建测试rule表
需求是：在表test_table 中创建一个规则，使得所有插入操作都会将数据插入到 audit_table 里
-- 创建测试表
caitdb=# CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
data TEXT
);
CREATE TABLE
-- 创建审计表
caitdb=# CREATE TABLE audit_table (
id SERIAL PRIMARY KEY,
original_id INT,
original_data TEXT,
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
-- 创建规则
caitdb=# CREATE RULE insert_into_audit AS
ON INSERT TO test_table
DO ALSO
INSERT INTO audit_table (original_id, original_data)
VALUES (NEW.id, NEW.data);
CREATE RULE
2、通过 PG_RULES 查询 rules 的定义
## 查看所有的规则
caitdb=# select * from pg_rules ;
## 查询规则定义
caitdb=# select definition from pg_rules where rulename='insert_into_audit';
definition
-----------------------------------------------------------------------
CREATE RULE insert_into_audit AS ON INSERT TO public.test_table DO INSERT INTO audit_table (original_id, original_data) VALUES (new.id, new.data);
(1 row)
caitdb=#
六、获取物化视图的ddl语句
1、创建物化视图
caitdb=# create materialized view view_company as select id,name,age,address,salary,join_date from company;
SELECT 8
caitdb=#
2、通过函数 pg_get_viewdef() 查询物化视图定义
caitdb=# select pg_get_viewdef('view_company');
pg_get_viewdef
------------------
SELECT id, +
name, +
age, +
address, +
salary, +
join_date +
FROM company;
(1 row)七、获取触发器的ddl语句
1、创建触发器
-- 创建触发器函数
caitdb=# CREATE OR REPLACE FUNCTION audit_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_table (original_id, original_data)
VALUES (NEW.id, NEW.data);
RETURN NEW; -- 对于 INSERT 操作，通常返回 NEW
END;
$$ LANGUAGE plpgsql;
2、查看触发器在function中查看
caitdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------+------------------+---------------------+-----
public | audit_insert | trigger | | func
public | test_function | void | | func
(2 rows)
3、使用函数 pg_get_functiondef() 查看触发器定义，不能使用 pg_get_triggerdef()
caitdb=# select pg_get_functiondef('audit_insert'::regproc);
pg_get_functiondef
----------------------------------------------------------
CREATE OR REPLACE FUNCTION public.audit_insert() +
RETURNS trigger +
LANGUAGE plpgsql +
AS $function$ +
BEGIN +
INSERT INTO audit_table (original_id, original_data)+
VALUES (NEW.id, NEW.data); +
RETURN NEW; -- 对于 INSERT 操作，通常返回 NEW +
END; +
$function$ +
(1 row)「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
