oracle有dbms_metadata.get_ddl;sqlserver可以直接右键;mysql有show create table
但pg好像没有直接的方法,网上找到了几种自己创建函数的方法,收集一下。
法一:
支持生成包含:字段(支持数组类型字段)、约束、索引(支持唯一索引、全类型索引)在内的建表语句。
CREATE OR REPLACE FUNCTION "public"."findattname"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "ctype" varchar)
RETURNS "pg_catalog"."varchar" AS $BODY$
declare
tt oid ;
aname character varying default '';
begin
tt := oid from pg_class where relname= tablename
and relnamespace =(select oid from pg_namespace where nspname=namespace and nspowner=(select datdba from pg_database where datname=dbinstancename) ) ;
aname:= array_to_string(
array(
select a.attname from pg_attribute a
where a.attrelid=tt and a.attnum in (
select unnest(conkey) from pg_constraint c where contype=ctype
and conrelid=tt and array_to_string(conkey,',') is not null
)
),',')
;
return aname;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
法二:
CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
RETURNS text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
BEGIN
FOR column_record IN
SELECT
b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
CASE WHEN
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
ELSE
''
END as column_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as column_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ ('^('||p_table_name||')$')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid=e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1 THEN
v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
ELSE
v_table_ddl:=v_table_ddl||',';
END IF;
IF column_record.attnum <= column_record.max_attnum THEN
v_table_ddl:=v_table_ddl||chr(10)||
' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
END IF;
END LOOP;
v_table_ddl:=v_table_ddl||');';
RETURN v_table_ddl;
END;
$BODY$
LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
下面是函数的用法:
SELECT generate_create_table_statement('tablename');
删除,DROP语句:
DROP FUNCTION generate_create_table_statement(p_table_name varchar);
法三:pg_dump表结构
pg_dump -t 'aschema.atable' --schema-only database-name
参考
如何在postgreSQL中为现有表生成"create table“sql语句 - 问答 - 腾讯云开发者社区-腾讯云