PostgreSQL 各类生成建表sql语句DDL方法收集， 函数获取数据库大小

生成的sql指定scheme为：【sch_租户id】，不同scheme生成规则或者不需要指定scheme直接修改下相关代码即可使用。

CREATE OR REPLACE FUNCTION "public"."findattname"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "ctype" varchar)
  RETURNS "pg_catalog"."varchar" 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 and nspowner=(select datdba from pg_database where datname=dbinstancename) ) ;      
       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

表创建sql生成

CREATE OR REPLACE FUNCTION "public"."showcreatetable"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "tenantid" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare 
tableScript character varying default '';
tableNum int2 ;
 
begin
-- check db extist
tableNum:= count(*)    from pg_class  where relname=tablename and relnamespace =(
            select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=dbinstancename) and nspname = namespace
            );
IF tableNum=0  then 
return '' ;
end if;
-- columns
tableScript:=tableScript || ' CREATE TABLE IF NOT EXISTS '|| '"sch_'||tenantid||'".'||tablename|| ' ( '|| array_to_string(
  array(
select concat( c1, c2, c3, c4, c5, c6 ) as column_line
from (
  select '"'||column_name||'"' || ' ' || case when data_type='ARRAY' then ltrim(udt_name,'_')||'[]' else data_type end 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 null end as c3,
    case when numeric_precision > 0 and numeric_scale > 0 then null 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_catalog=dbinstancename
  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(dbinstancename,namespace,tablename,'u') ) ||')' end as u ,
        case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(dbinstancename,namespace,tablename,'p') ) ||')' end  as p  ,
        case when contype='f' then ' FOREIGN KEY('|| ( select findattname(dbinstancename,namespace,tablename,'u') ) ||') REFERENCES '|| 
        (select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select findattname(dbinstancename,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=dbinstancename) and nspname = namespace
            )
         )
    ) as t  
) ,',' ) || ' ); '; 
 
-- 
/** **/
--- 获取非约束索引 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 '||case when is_unique_index=true then 'UNIQUE INDEX' else 'INDEX' end ||'"' || indexrelname ||'"' || ' ON ' || '"sch_'||tenantid||'".'||tablename|| ' USING '||index_type|| '(' || 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, x.indisunique is_unique_index,am.amname index_type
            
           FROM pg_class c
           JOIN pg_index x ON c.oid = x.indrelid
           JOIN pg_class i ON i.oid = x.indexrelid
             join pg_am am on am.oid = i.relam
           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  )
                and c.relnamespace =(
            select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=dbinstancename) and nspname = namespace
            )
        )as t
) ,'' );
            

 
return tableScript;
 
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

–PG版本： PostgreSQL 11.11
–pg存储过程获取表创建sql，数据库表ddl

--PG版本： PostgreSQL 11.11

--pg存储过程获取表创建sql，数据库表ddl：

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;begin 

    --获取表的pid、schema信息

    open curs for 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 ~ ('^('||tab_name||')$')AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2,3;

    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

        IF tab_info.attnum = 1 THEN

            tab_ddl:='CREATE TABLE '||tmp_col.nspname||'.'||tmp_col.relname||' (';

        ELSE

            tab_ddl:=tab_ddl||',';

        END IF;

        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||');';

    --输出结果

    RETURN tab_ddl;end;$$ language plpgsql;



--用法：

select get_tab_ddl('table_name');

获取数据库大小

--获取数据库大小
CREATE OR REPLACE FUNCTION get_dbsize(dbname varchar)
RETURNS  VARCHAR AS
$FUNCTION$
DECLARE
	isExists 			varchar;
	dbsize 				varchar;
	databasename 		varchar;
BEGIN
	select datname into databasename from pg_database where lower(datname) = dbname;
	if databasename != '' THEN
		select pg_size_pretty(pg_database_size(lower(dbname))) into dbsize;
		return dbsize;
	else
		raise notice 'The database: % does not exists ! ',lower(dbname);
	end if;
END;
$FUNCTION$
LANGUAGE PLPGSQL;

表创建sql

--创建 get_table_structure 函数
CREATE PROCEDURAL LANGUAGE plpythonu;
create or replace function get_table_structure(tablename text)
    returns text
as $$
    try:
        table_name = tablename.lower().split('.')[1]
        talbe_schema=tablename.lower().split('.')[0]
    except (IndexError):
        return 'Please in put "tableschema.table_name"'
    get_table_oid="select oid,reloptions,relkind from pg_class where oid='%s'::regclass"%(tablename)
    try:
        rv_oid=plpy.execute(get_table_oid,5)
        if not rv_oid:
            return 'Did not find any relation named"'+tablename +'".'
    except (NameError):
        return 'Did not find any relation named"'+tablename +'".'
    table_oid=rv_oid[0]['oid']
    rv_reloptions=rv_oid[0]['reloptions']
    rv_relkind=rv_oid[0]['relkind']
    create_sql="";
    table_kind='table';
    if rv_relkind !='r' and rv_relkind !='v':
        plpy.error('%s is not table or view'%(tablename));
    elif rv_relkind=='v':
        get_view_def="select pg_get_viewdef(%s,'t') as viewdef;" % (table_oid)
        rv_viewdef=plpy.execute(get_view_def);
        create_sql='create view %s as \n' % (tablename)
        create_sql += rv_viewdef[0]['viewdef']+'\n';
        table_kind='view'
    else:
        get_columns="select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),\
       (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) \
        as default,a.attnotnull as isnull from pg_catalog.pg_attribute \
        a where a.attrelid= %s and a.attnum >0 and not a.attisdropped order by a.attnum;" % (table_oid);
        rv_columns=plpy.execute(get_columns)
        
    
        get_table_distribution1="select attrnums from pg_catalog.gp_distribution_policy t where localoid = '" + table_oid + "' "
        rv_distribution1=plpy.execute(get_table_distribution1,500)
        rv_distribution2=''
        if rv_distribution1 and rv_distribution1[0]['attrnums']:
            get_table_distribution2="select attname from pg_attribute where attrelid='"+table_oid+"' and attnum in (" + str(rv_distribution1[0]['attrnums']).strip('{').strip('}').strip('[').strip(']')+")"
            rv_distribution2=plpy.execute(get_table_distribution2,500)
    
        create_sql='create table %s (\n' % (tablename)
        get_index="select pg_get_indexdef(indexrelid) as indexdef from pg_index where indrelid=%s" % (table_oid);
        rv_index=plpy.execute(get_index);

        
        get_parinfo1="select attname as columnname from pg_attribute where attnum =(select paratts[0] from pg_partition where parrelid=%s) and attrelid=%s;"%(table_oid,table_oid);
        get_parinfo2=""" select pp.parrelid,prl.parchildrelid,case when pp.parkind='h'::"char" then 'hash'::text when pp.parkind='r'::"char" then 'range'::text when pp.parkind='l'::"char" then 'list'::text else null::text end as partitiontype,pg_get_partition_rule_def(prl.oid,true) as partitionboundary from pg_partition pp,pg_partition_rule prl where pp.paristemplate=false and pp.parrelid = %s and prl.paroid = pp.oid order by prl.parname; """ % (table_oid)
        v_par_parent=plpy.execute(get_parinfo1);
        v_par_info=plpy.execute(get_parinfo2);
        max_column_len=10
        max_type_len=4
        max_modifiers_len=4
        max_default_len=4
        for i in rv_columns:
            if i['attname']:
                if max_column_len < i['attname'].__len__():
                    max_column_len=i['attname'].__len__()
            if i['format_type']:
                if max_type_len < i['format_type'].__len__():
                    max_type_len=i['format_type'].__len__()
            if i['default']:
                if max_type_len < i['default'].__len__():
                    max_default_len=i['default'].__len__()
        first=True
        for i in rv_columns:
            if first==True:
                split_char=' ';
                first=False
            else:
                split_char=',';
            if i['attname']:
                create_sql += " " + split_char + i['attname'].ljust(max_column_len+6)+''
            else:
                create_sql += "" + split_char + ' '.ljust(max_column_len+6)
            if i['format_type']:
                create_sql += ' ' + i['format_type'].ljust(max_type_len +2)
            else:
                create_sql += ' ' + ' '.ljust(max_type_len+2)
            if i['isnull'] and i['isnull']:
                create_sql += ' ' + ' not null '.ljust(8)
            if i['default']:
                create_sql += ' default ' + i['default'].ljust(max_default_len+6)
            create_sql += "\n"
        create_sql += ")"
 
        if rv_reloptions:
            create_sql +=" with ("+str(rv_reloptions).strip('{').strip('}').strip('[').strip(']') +")\n"
            create_sql = create_sql.replace("'",'')
        if rv_distribution2:
            create_sql += 'Distributed by ('
            for i in rv_distribution2:
                create_sql += i['attname'] + ','
            create_sql =create_sql.strip(',')+')'
        elif rv_distribution1:
            create_sql += 'Distributed randomly\n'
        if v_par_parent:
            partitiontype=v_par_info[0]['partitiontype'];
            create_sql +='\nPARTITION BY '+ partitiontype + "("+v_par_parent[0]['columnname']+")\n(\n";
            for i in v_par_info:
                create_sql +=" " +i['partitionboundary']+',\n';
            create_sql=create_sql.strip(',\n');
            create_sql+="\n)"
        create_sql+=";\n\n"
        for i in rv_index:
            create_sql += i['indexdef']+';\n'

        
        get_table_comment="select 'comment on %s %s is '''|| COALESCE (description,'')|| '''' as comment from pg_description where objoid=%s and objsubid=0;" % (table_kind,tablename,table_oid)
        get_column_comment="select 'comment on column %s.'||b.attname ||' is ''' || COALESCE(a.description,'')|| ''' ' as comment from pg_catalog.pg_description a,pg_catalog.pg_attribute b where objoid=%s and a.objoid=b.attrelid and a.objsubid=b.attnum;" % (tablename,table_oid)
        rv_table_comment=plpy.execute(get_table_comment);
        rv_column_comment=plpy.execute(get_column_comment);


        for i in rv_table_comment:
            create_sql += i['comment']+';\n'
        for i in rv_column_comment:
            create_sql +=i['comment']+';\n'
        return create_sql;

$$ LANGUAGE plpythonu;

--获取数据库中所有表的建表语句
创建存放建表语句的表
create table tab_structure(context text) distributed by context;
CREATE OR REPLACE FUNCTION get_cts(username varchar)
RETURNS void AS
$FUNCTION$
DECLARE 
s_tab 					varchar;
schema_name 			varchar;
tabname 				varchar;
vsql					text;
table_name cursor for select tablename from pg_tables where lower(tableowner) = username;
BEGIN
	truncate table tab_structure;
	select schemaname into schema_name from pg_tables where lower(tableowner) = username;
	open table_name;
	loop
		fetch table_name into tabname;
		if not found then
			exit;
		end if;
		s_tab = schema_name ||'.' || tabname;
		vsql = 'insert into tab_structure select get_table_structure('''||s_tab||''')';
		execute vsql;
		raise notice '%  ',s_tab ;
	end loop;
END;
$FUNCTION$
LANGUAGE PLPGSQL;

使用

# select get_cts('test'); --此处的test为用户名

# psql -U test -d testdb -At -c "select * from tab_structure" >cts.sql