Pgsql生成建表语句带注释

创建建表语句函数

create or replace function get_tab_ddl(tab_name varchar)returns text as $$declare

    --定义变量
	tab_ddl text;

	curs refcursor;
	tmp_col record;
	tab_info record;
	comm_info record;
	
	begin 
		--获取表的pid、schema信息
		open curs for 
		
		SELECT 
		c.oid,
		n.nspname,
		c.relname,
		cast(obj_description(c.oid) as varchar) as table_description
		FROM pg_catalog.pg_class c    
		LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace    
		WHERE c.relname ~ ('^('||tab_name||')$')
		AND pg_catalog.pg_table_is_visible(c.oid) 
		ORDER BY 2,3;
		
		-- 将数据存入tmp_col
		fetch curs into tmp_col;

		--判断是否存在该表

		if tmp_col.oid is null then
			return 'Table "'||tab_name||'" was not queried';
		end if;

		-- 如表存在，获取表的列信息
		-- 获取建表信息
		FOR tab_info IN
			SELECT
				a.attname as col_name,
				pg_catalog.format_type(a.atttypid, a.atttypmod) as col_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 col_default_value,

				CASE WHEN a.attnotnull = true 
				THEN
					'NOT NULL'
				ELSE
					'NULL'
				END as col_not_null,
				a.attnum as attnum,
				e.max_attnum as max_attnum
			FROM
			pg_catalog.pg_attribute a   
			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 a.attrelid=tmp_col.oid          
			AND NOT a.attisdropped        
			ORDER BY a.attnum
				
		-- 拼接为ddl语句

		LOOP
			-- attnum = 1时建表头，否则为末尾逗号
			IF tab_info.attnum = 1 THEN
				tab_ddl:='CREATE TABLE '||tmp_col.relname||' (';
			ELSE
				tab_ddl:=tab_ddl||',';
			END IF;
			-- chr(10)代表换行符
			IF tab_info.attnum <= tab_info.max_attnum THEN
				tab_ddl:=tab_ddl||chr(10)||'    '||tab_info.col_name||' '||tab_info.col_type||' '||tab_info.col_default_value||' '||tab_info.col_not_null;
			END IF;

		END LOOP;
		
		-- 建表语句结束
		tab_ddl:=tab_ddl||');';
		  
		-- 获取注释信息
		FOR comm_info IN
			SELECT
				a.attname as col_name,
				a.attnum as attnum,
				e.max_attnum as max_attnum,
				t2.description as col_description 
			FROM
			pg_catalog.pg_attribute a   
			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 
			-- 添加注解表
			INNER join pg_catalog.pg_description t2	ON  a.attrelid =  t2.objoid and  a.attnum = t2.objsubid
			WHERE a.attnum > 0
			AND a.attrelid=tmp_col.oid          
			AND NOT a.attisdropped        
			ORDER BY a.attnum
				
		-- 拼接为ddl语句
		  
		loop
			IF tmp_col.table_description is not null THEN
			-- attnum = 1添加表名注释，否则为末尾分号
				IF comm_info.attnum = 1 THEN
					tab_ddl:=tab_ddl||'COMMENT ON TABLE '||tmp_col.relname||' IS '||chr(39)||tmp_col.table_description||chr(39)||' ;';
				END IF;
			 END if;
			 IF comm_info.col_description is not null THEN
				-- chr(10)代表换行符||代表加号
				IF comm_info.attnum <= comm_info.max_attnum THEN
					tab_ddl:=tab_ddl||chr(10)||'COMMENT ON COLUMN '||'    '||comm_info.col_name||' '||' IS '||' '||chr(39)||comm_info.col_description||chr(39)||' ;';
				END IF;
			 END if;

		END LOOP;
		--输出结果
		RETURN tab_ddl;
		
	end;
	$$ 
	language plpgsql;

调用建表语句

	select get_tab_ddl('t_user');