PostgreSQL 函式獲取表DDL
CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS text LANGUAGE sql STRICT AS $$ WITH attrdef AS ( SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts, c.relpersistence, a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault, a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation, a.attidentity, a.attgenerated FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE n.nspname = $1 AND c.relname = $2 AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ), coldef AS ( SELECT attrdef.nspname, attrdef.relname, attrdef.oid, attrdef.relopts, attrdef.relpersistence, pg_catalog.format('%I %s%s%s%s%s', attrdef.attname, attrdef.atttype, case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end, case when attrdef.attnotnull then ' NOT NULL' else '' end, case when attrdef.attdefault is null then '' else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault) when attrdef.attgenerated <> '' then ' GENERATED AS NOT_IMPLEMENTED' else pg_catalog.format(' DEFAULT %s', attrdef.attdefault) end end, case when attrdef.attidentity<>'' then pg_catalog.format(' GENERATED %s AS IDENTITY', case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end) else '' end ) as col_create_sql FROM attrdef ORDER BY attrdef.attnum ), tabdef AS ( SELECT coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence, concat(string_agg(coldef.col_create_sql, E',\n ') , (select concat(E',\n ',pg_get_constraintdef(oid)) from pg_constraint where contype='p' and conrelid = coldef.oid)) as cols_create_sql FROM coldef GROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence ) SELECT FORMAT( 'CREATE%s TABLE %I.%I%s%s%s;', case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end, tabdef.nspname, tabdef.relname, coalesce( ( SELECT FORMAT( E'\n PARTITION OF %I.%I %s\n', pn.nspname, pc.relname, pg_get_expr(c.relpartbound, c.oid) ) FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelid JOIN pg_class pc ON pc.oid = i.inhparent JOIN pg_namespace pn ON pn.oid = pc.relnamespace WHERE c.oid = tabdef.oid ), FORMAT( E' (\n %s\n)', tabdef.cols_create_sql) ), case when tabdef.relopts <> '' then format(' WITH (%s)', tabdef.relopts) else '' end, coalesce(E'\nPARTITION BY '||pg_get_partkeydef(tabdef.oid), '') ) as table_create_sql FROM tabdef $$;
使用方式
postgres=# select * from tabledef('public','t2'); tabledef -------------------------- CREATE TABLE public.t2 (+ id integer, + info text + ); (1 row)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69990629/viewspace-2909221/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle之 獲取建表ddl語句Oracle
- Citus 分散式 PostgreSQL 叢集 - SQL Reference(建立和修改分散式表 DDL)分散式SQL
- oracle獲取ddl指令碼Oracle指令碼
- Oracle 獲取ddl語句Oracle
- 獲取建tablespace sql ddlSQL
- DB2_使用表函式獲取健康監視器快照DB2函式
- PostgreSQL獲取建表語句儲存過程SQL儲存過程
- PostgreSQL 建立主鍵自增表的 DDLSQL
- php 獲取函式被呼叫位置PHP函式
- 用dbms_metadata.get_ddl獲取ddl語句
- DBMS_METADATE.GET_DDL獲取物件DDL指令碼物件指令碼
- PostgreSQL函式:返回表查詢結果集SQL函式
- 獲取物件DDL語句的方法物件
- C語言getgroups()函式:獲取組程式碼函式C語言函式
- PostgreSQL 原始碼解讀(121)- MVCC#6(獲取事務號-實現函式)SQL原始碼MVCC#函式
- 視窗屬性的獲取函式函式
- 用前面建立的函式獲取物件函式物件
- DBMS_METADATA.GET_DDL獲取使用者ddl指令碼指令碼
- 使用DBMS_METADATA.GET_DDL獲取物件的DDL指令碼物件指令碼
- 獲取單個檢視DDL指令碼指令碼
- shell指令碼獲取函式返回值指令碼函式
- 獲取javascript函式形參的數目JavaScript函式
- 反射如何獲取函式的引數名反射函式
- sql 獲取系統時間的函式。SQL函式
- PostgreSQL:視窗函式SQL函式
- PostgreSQL函式裡呼叫函式(SETOF + RETURN QUERY)SQL函式
- oracle dbms_metadata 獲取ddl語句Oracle
- QT槽函式獲取訊號傳送物件QT函式物件
- C++ 獲取指定的過載函式地址C++函式
- 用JS獲取函式相關的程式碼JS函式
- 核心分析PE獲取DLL匯出函式地址函式
- oracle lock dba_ddl_locks獲取表級共享鎖及表級排它鎖之系列八Oracle
- PostgreSQL DBA(73) - dump函式SQL函式
- PostgreSQL/MogDB/openGauss怎樣獲取表上依賴於該表的檢視SQL
- Oracle中獲取TABLE的DDL語句的方法Oracle
- 通過dblink獲取遠端DDL語句
- MogDB/openGauss學習筆記-獲取物件DDL筆記物件
- [譯] 使用 closest() 函式獲取正確的 DOM 元素函式