PostgreSQL获取数据表创建语句

 PostgreSQL获取数据表创建语句如下

SELECT array_to_string( ARRAY (
		SELECT ret  FROM
			(
					SELECT
						'CREATE TABLE 模式名称.表名称 (' || array_to_string(ARRAY (
										SELECT SQL 
										FROM
											(
												(--字段信息
													SELECT array_to_string(ARRAY (
													SELECT A.attname || ' ' || concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) || ' ' ||
																CASE A.attnotnull  WHEN 't' THEN 'NOT NULL' ELSE '' END || ' ' ||
																CASE WHEN D.adbin IS NOT NULL THEN ' DEFAULT ' || pg_get_expr ( D.adbin, A.attrelid ) ELSE'' END 
													FROM
														pg_attribute
														A LEFT JOIN pg_description b ON A.attrelid = b.objoid 
														AND A.attnum = b.objsubid
														LEFT JOIN pg_attrdef D ON A.attrelid = D.adrelid 
														AND A.attnum = D.adnum,
														pg_type T 
													WHERE
														A.attstattarget =- 1 
														AND A.attrelid = '模式名称.表名称' :: REGCLASS :: OID 
														AND A.attnum > 0 
														AND NOT A.attisdropped 
														AND A.atttypid = T.OID 
													ORDER BY A.attnum  ),',' || CHR( 10 )  )  SQL, 1 seri 
												) 
												UNION
												(--约束（含主外键）信息
													SELECT 'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef ( OID ) SQL, 2 seri 
													FROM pg_constraint T 
													WHERE conrelid = '模式名称.表名称' :: REGCLASS :: OID 
													ORDER BY contype DESC 
												) 
												ORDER BY seri  
											) T 
									),',' || CHR( 10 )  ) || ')' AS ret, 1 AS orderby 
					UNION --索引信息
							SELECT array_to_string(ARRAY ( 
											SELECT pg_get_indexdef ( indexrelid ) 
											FROM pg_index 
											WHERE indrelid = '模式名称.表名称' :: REGCLASS :: OID 
											AND indisprimary = 'f' 
											AND indisunique = 'f' 
									),';' || CHR( 10 ) ) AS ret,2 AS orderby 
					UNION --注释信息
							SELECT array_to_string(ARRAY (
												SELECT
													'COMMENT ON COLUMN ' || '模式名称.表名称.' || A.attname || ' IS ''' || b.description || '''' 
												FROM pg_attribute A 
												LEFT JOIN pg_description b ON A.attrelid = b.objoid 
													AND A.attnum = b.objsubid 
												WHERE
													A.attstattarget =- 1 
													AND A.attrelid = '模式名称.表名称' :: REGCLASS :: OID 
													AND b.description IS NOT NULL 
												ORDER BY
													A.attnum 
									), ';' || CHR( 10 )  ) AS ret,3 AS orderby 
						ORDER BY orderby 
			) results 
		), ';' || CHR( 10 ) || CHR( 13 ) 
)
 

效果如下

CREATE TABLE public.t_type (ca numeric(6,2)   DEFAULT 123,
cnnull varchar(255) NOT NULL  DEFAULT 'abc'::character varying,
ccheck varchar(255)  ,
cpkey varchar(255) NOT NULL ,
cfkey int4 NOT NULL ,
CONSTRAINT cchk CHECK ((length((ccheck)::text) > 0)),
CONSTRAINT confkey FOREIGN KEY (cfkey) REFERENCES company(id) ON DELETE CASCADE,
CONSTRAINT t1_pkey PRIMARY KEY (cpkey),
CONSTRAINT uniquecolumn UNIQUE (ca));

CREATE INDEX idx ON public.t_type USING btree (ccheck);
CREATE INDEX idx2 ON public.t_type USING btree (cnnull);

COMMENT ON COLUMN "public".t_type.cnnull IS '注释';
COMMENT ON COLUMN "public".t_type.cfkey IS '注释12123'