GP上編寫python函數(shù)獲取表和視圖結(jié)構(gòu)

1、create or replace function get_table_structure(tablename text)returns textas $try:table_name = tablename.lower().split('.')1talbe_schema=tablename.lower().split('.')0except (IndexError):return 'Please in put "tableschema.table_name"'whereget_table_oid="select o

2、id,reloptions,relkind from pg_classoid='%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 +'

3、;".'table_oid=rv_oid0'oid'rv_reloptions=rv_oid0'reloptions'rv_relkind=rv_oid0'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=&#

4、39;v':get_view_def="select pg_get_viewdef(%s,'t') as viewdef;" % (table_oid)rv_viewdef=plpy.execute(get_view_def);create_sql='CREATE OR REPLACE %s as n' % (tablename)create_sql += rv_viewdef0'viewdef'+'n'table_kind='view'else:get_columns="se

5、lect 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.at

6、trelid= %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 twhere localoid = '" + table_oid + "' "rv_distribution1=plpy.exe

7、cute(get_table_distribution1,500) rvdistribution2='' if rv_distribution1 and rv_distribution10'attrnums':get_table_distribution2="select attname from pg_attribute whereattrelid='"+table_oid+"'andattnumin("+str(rv_distribution10'attrnums').strip(

8、9;').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 whereindrelid=%s" % (table_oid)

9、;rv_index=plpy.execute(get_index);get_parinfo1="select attname as columnname from pg_attribute where attnum=(select paratts0 from pg_partition where parrelid=%s) and attrelid=%s;"%(table_oid,table_oid);get_parinfo2="""select pp.parrelid,prl.parchildrelid,casewhenpp.parkind=&

10、#39;h':"char" then 'hash':text when pp.parkind='r':"char" then 'range':text whenpp.parkind='l':"char"then 'list':text else null:text end aspartitiontype,pg_get_partition_rule_def(prl.oid,true) as partitionboundary from pg_part

11、itionpp,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=10max_type_len=4max_modifiers_len=4max_default_len

12、=4for 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

13、'default'._len_():max_default_len=i'default'._len_()first=Truefor i in rv_columns:if first=True:split_char=' 'first=Falseelse:split_char=','if i'attname':create_sql += " " + split_char + i'attname'.ljust(max_column_len+6)+'' else:crea

14、te_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 += '

15、 ' + ' 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('').s

16、trip('').strip('')+")n"create_sql = create_sql.replace("'",'')if rv_distribution2:create_sql += 'Distributed by ('for i in rvdistribution2:create_sql += i'attname' + ',create_sql =create_sql.strip(',')+')'elif rv_dis

17、tribution1:create_sql += 'Distributed randomlyn'if v_par_parent:partitiontype=v_par_info0'partitiontype'create_sql +='nPARTITION BY '+ partitiontype + "("+v_par_parent0'columnname'+")n(n"for i in v_par_info:create_sql +=" " +i'partiti

18、onboundary'+',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;"