在mysql 中show create table 可以直接查询表的create sql 语句,在postgreSQL 没有这个命令,所以通过function 来实现,代码如下:
前提 定义一个公用的函数:findattname
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:
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.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname=tablename and i.relname not in ( select constraint_name from information_schema.key_column_usage where table_name=tablename ) )as t ) ,',' ); -- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键'; tableScript:= tableScript || array_to_string( array( SELECT ' COMMENT ON COLUMN' || tablename || '.' || a.attname ||' IS '|| ''''|| d.description ||'''' FROM pg_class c JOIN pg_description d ON c.oid=d.objoid JOIN pg_attribute a ON c.oid = a.attrelid WHERE c.relname=tablename AND a.attnum = d.objsubid),',') ; return tableScript; end $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
测试:
select showcreatetable('public','pg_seclabel');
相关推荐
博客:PostgreSQL的学习心得和知识总结(五十六)|语法级自上而下完美实现MySQL数据库的 show create table 的实现方案
诚然SQLite允许忽略数据类型 但是仍然建议在你的Create Table语句中指定数据类型 因为数据类型对于你和其他的程序员交流 或者你准备换掉你的数据库引擎 SQLite支持常见的数据类型 如: CREATE TABLE ex2 a ...
终极SQL课程 资料库 具有可访问界面的结构化计算机数据集 资料收集 ... 创建表:CREATE TABLE猫(必须始终为复数) 例子: CREATE TABLE tablename ( column_name data_type, column_name data_
诚然SQLite允许忽略数据类型, 但是仍然建议在你的Create Table语句中指定数据类型. 因为数据类型对于你和其他的程序员交流, 或者你准备换掉你的数据库引擎. SQLite支持常见的数据类型, 如: CREATE TABLE ex2( ...
1. Create table命令 用于创建表。在创建表时,经常会创建该表的主键、外键、唯一约束、Check约束等 语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1...
Create web services that are lightweight, maintainable, scalable, and secure using the best tools and techniques designed for Python About This Book Develop RESTful Web Services using the most popular...
This book sets up a project to show you the different steps, processes, and tools in continuous delivery and the actual problems they solve. We start by introducing Continuous Integration (CI), ...
"Show Keys and Indexed Columns" is preset for all assistance types by default. Primary keys, foreign keys, and indexed columns are displayed by default in column popups and mouse-over hints. Mouse-...
| Create_tmp_table_priv | enum | | Create_user_priv | enum | | Create_view_priv | enum | | Delete_priv | enum | | Drop_priv | enum | | Execute_priv | enum | | File_priv | enum | | Grant_priv | ...
+ 5.4.2 SELECT INTO TABLE + 5.4.3 事务(Transactions) + 5.4.4 存储过程和触发器 + 5.4.5 外键(Foreign Keys) # 5.4.5.1 不使用外键的理由 + 5.4.6 视图(Views) + 5.4.7 '--'作为一个 注解的开始 o 5.5 ...
7.6 DROP DATABASE (抛弃数据库)句法 7.7 CREATE TABLE (创建表)句法 7.7.1 隐含(silent)的列指定变化 7.8 ALTER TABLE (改变表)句法 7.9 OPTIMIZE TABLE (优化表) 句法 7.10 DROP TABLE ...
7.4.13 与GROUP BY子句一起使用的函数 7.5 CREATE DATABASE (创建数据库)句法 7.6 DROP DATABASE (抛弃数据库)句法 7.7 CREATE TABLE (创建表)句法 7.7.1 隐含(silent)的列指定变化 ...
;;;;;;;;... 1.... 2.... 3.... 4.... 5.... 6.... The syntax of the file is extremely simple.... Section headers (e.g.... at runtime.... There is no name validation.... (e.g.... previously set variable or directive (e.g....