postgreSQL实现show create table
postgreSQL实现show create table
在mysql 中show create table 可以直接查询表的create sql 语句，在postgreSQL 没有这个命令，所以通过function 来实现，代码如下：
www.zzzyk.com
前提 定义一个公用的函数：findattname
Sql代码
CREATE OR REPLACE FUNCTION findattname(namespace character varying, tablename character varying, ctype character varying)
RETURNS character varying AS
$BODY$
declare
tt oid ;
aname character varying default '';
begin
tt := oid from pg_class where relname= tablename
and relnamespace =(select oid from pg_namespace where nspname=namespace) ;
-- select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=namespace)
aname:= array_to_string(
array(
select a.attname from pg_attribute a
where a.attrelid=tt and a.attnum in (
select unnest(conkey) from pg_constraint c where contype=ctype
and conrelid=tt and array_to_string(conkey,',') is not null
)
),',')
;
return aname;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
showcreatetable：
Sql代码
CREATE OR REPLACE FUNCTION showcreatetable(namespace character varying, tablename character varying)
RETURNS character varying AS
$BODY$
declare
tableScript character varying default '';
begin
-- columns
tableScript:=tableScript || ' CREATE TABLE '|| tablename|| ' ( '|| array_to_string(
array(
select concat( c1, c2, c3, c4, c5, c6 ) as column_line
from (
select column_name || ' ' || data_type as c1,
case when character_maximum_length > 0 then '(' || character_maximum_length || ')' end as c2,
case when numeric_precision > 0 and numeric_scale < 1 then '(' || numeric_precision || ')' end as c3,
case when numeric_precision > 0 and numeric_scale > 0 then '(' || numeric_precision || ', ' || numeric_scale || ')' end as c4,
case when is_nullable = 'NO' then ' NOT NULL' end as c5,
case when column_default is not Null then ' DEFAULT' end || ' ' || replace(column_default, '::character varying', '') as c6
from information_schema.columns
where table_name = tablename
-- and table_schema=namespace
order by ordinal_position
) as string_columns
),' , ') ||',' ;
-- 约束
tableScript:= tableScript || array_to_string(
array(
select concat(' CONSTRAINT ',conname ,c ,u,p,f) from (
select conname,
case when contype='c' then ' CHECK('|| consrc ||')' end as c ,
case when contype='u' then ' UNIQUE('|| ( select findattname(namespace,tablename,'u') ) ||')' end as u ,
case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(namespace,tablename,'p') ) ||')' end as p ,
case when contype='f' then ' FOREIGN KEY('|| ( select findattname(namespace,tablename,'u') ) ||') REFERENCES '||
(select p.relname from pg_class p where p.oid=c.confrelid ) || '('|| ( select findattname(namespace,tablename,'u') ) ||')' end as f
from pg_constraint c
where contype in('u','c','f','p') and conrelid=(
select oid from pg_class where relname=tablename and relnamespace =(
select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=namespace)
)
)
) as t
) ,',' ) || ' ); ';
-- indexs
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
--
/** **/
--- 获取非约束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || array_to_string(
array(
select 'CREATE UNIQUE INDEX ' || indexrelname || ' ON ' || tablename || ' USING btree '|| '(' || attname || ');' from (
SELECT
i.relname AS indexrelname , x.indkey,
( select array_to_string (
array(
select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )
)
,',' ) )as attname
FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i
