1、获取指定模式下的用户表名
select tablename as name,'表' as tb from pg_tables where schemaname='" + schema + "'
2、获取表字段名、数据类型、长度、描述
select name,reftype,length,descrip from ( select a.attnum ,c.relname ,a.attname AS name,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as reftype,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as length,d.description AS descrip from pg_class c, pg_attribute a , pg_type t, pg_description d where c.relname = '" + table_name + "' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum ORDER BY a.attnum asc) as t
3、获取建表语句
select showcreatetable('"+.database_schema+"','"+table_name+"');
4、获取主键
SELECT pg_attribute.attname AS colname FROM pg_constraint INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = pg_constraint.conkey [ 1 ] INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid WHERE pg_class.relname = '" + s_tb.table_name_eng + "' AND pg_constraint.contype = 'p';