greenplum获取建表语句_greenplum通过函数获取指定用户下的所有建表语句

函数实现

--获取数据库大小

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;

--创建 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;

vsqltext;

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;

测试：

执行获取整个用户名对象下的建表语句函数

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

NOTICE: public.t

NOTICE: public.t1

NOTICE: public.tab_structure

get_cts

---------

(1 row)

查看存放建表语句的表 tab_structure;

testdb=# select * from tab_structure ;

context

----------------------------------------

create table public.t (

id integer

)Distributed by (id);

create table public.t1 (

id integer

,name character varying

)Distributed by (id);

create table public.tab_structure (

context text

)Distributed by (context);

因为get_table_structure(schema.tablename)这个函数，一次只能获取一张表的表结构。

因此如果不使用pg_dump，仅仅是为了获取所有表的原始建表语句，可以使用get_cts(username) 这个函数

将原始建表语句存放于文件中执行以下命令：

[gpadmin@mpp01 ~]$ psql -U test -d testdb -At -c "select get_cts('test')"

NOTICE: public.t

NOTICE: public.t1

NOTICE: public.tab_structure

[gpadmin@mpp01 ~]$ psql -U test -d testdb -At -c "select * from tab_structure" >cts.sql

查看文件cts.sql

[gpadmin@mpp01 ~]$ cat cts.sql

create table public.t (

id integer

)Distributed by (id);

create table public.t1 (

id integer

,name character varying

)Distributed by (id);

create table public.tab_structure (

context text

)Distributed by (context);

很方便，供参考。