共计 1773 个字符，预计需要花费 5 分钟才能阅读完成。
PG 版本：
PostgreSQL 11.11
存储过程：
create or replace function get_tab_ddl(tab_name varchar)returns text as $$declare
-- 定义变量
tab_ddl text;
curs refcursor;
tmp_col record;
tab_info record;begin
-- 获取表的 pid、schema 信息
open curs for 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 ~ ('^('||tab_name||')$')AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2,3;
fetch curs into tmp_col;
-- 判断是否存在该表
if tmp_col.oid is null then
return 'Table"'||tab_name||'"was not queried';
end if;
-- 如表存在，获取游戏列表信息
FOR tab_info IN
SELECT
a.attname as col_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as col_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 col_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as col_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum FROM
pg_catalog.pg_attribute a INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum FROM pg_catalog.pg_attribute a WHERE a.attnum > 0
AND NOT www.cungun.coma.attisdropped GROUP BY a.attrelid) e ON a.attrelid=e.attrelid WHERE a.attnum > 0
AND a.attrelid=tmp_col.oid AND NOT a.attisdropped ORDER BY a.attnum -- 拼接为 ddl 语句
LOOP
IF tab_info.attnum = 1 THEN
tab_ddl:='CREATE TABLE'||tmp_col.nspname||'.'||tmp_col.relname||'(';
ELSE
tab_ddl:=tab_ddl||',';
END IF;
IF tab_info.attnum <= tab_info.max_attnum THEN
tab_ddl:=tab_ddl||chr(10)||''||tab_info.col_name||' '||tab_info.col_type||' '||tab_info.col_default_value||' '||tab_info.col_not_null;
END IF;
END LOOP;
tab_ddl:=tab_ddl||');';
-- 输入后果
RETURN tab_ddl;end;$$ language plpgsql;
游戏列表用法：
select get_tab_ddl(‘table_name’);
正文完
