1、创建支持的pythonu语言
代码语言：javascript
复制CREATE PROCEDURAL LANGUAGE plpythonu;
或
createlang plpythonu -d stagging;
2、创建get_table_structure函数
代码语言：javascript
复制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 (Error):
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 randomlyn'
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 =";nn"
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;
3、测试get_table_structure函数
代码语言：javascript
复制select get_table_structure('xiaoxu.b_tree_test');
create table xiaoxu.b_tree_test (
s_ext_nodenum integer
,pripid character varying
,s_ext_sequence character varying
********
) with (appendonly=true, compresstype=zlib, compresslevel=5, orientation=column, checksum=false, blocksize=2097152)
Distributed by (pripid);
CREATE INDEX idx_t_btree_xiaoxu ON b_tree_test USING btree (s_ext_nodenum);
