postgresql查看表语句_PostgreSQL查看或生成表的建表语句

连接pg的jdbc的url格式：jdbc:postgresql://ip:port/dbname

查看指定库的所有表和视图信息

--法一：查询当前登录url所在可下public的

SELECT a.oid,

a.relname AS name,

b.description AS comment

FROM pg_class a

LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid

WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下

AND a.relkind='r'

ORDER BY a.relname;

--表信息

select * from pg_tables where schemaname='public' and tableowner='db_name';

--视图的定义

select definition from pg_views where viewname='xxxx' and viewowner='xxxxx';

查看表的建表信息

--法一

SELECT a.attnum,

a.attname AS field,

t.typname AS type,

a.attlen AS length,

a.atttypmod AS lengthvar,

a.attnotnull AS notnull,

b.description AS comment

FROM pg_class c,

pg_attribute a

LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,

pg_type t

WHERE c.relname = 'xxxxxx'

and a.attnum > 0

and a.attrelid = c.oid

and a.atttypid = t.oid

ORDER BY a.attnum;

--法二

select

col.table_schema,

col.table_name,

col.ordinal_position,

col.column_name,

col.data_type,

col.character_maximum_length,

col.numeric_precision,

col.numeric_scale,

col.is_nullable,

col.column_default,

des.description

from

information_schema.columns col left join pg_description des on

col.table_name::regclass = des.objoid

and col.ordinal_position = des.objsubid

where

table_schema = 'public'

and table_name = 'xxxxxx'

order by

ordinal_position;

生成表的建表语句

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;