获取gp表结构的函数

说明：此函数可以获取二级分区表以内的表结构，里面添加了一些关键字过滤，可能不全，遇到时可自行添加

用法：select get_gp_create_sql('schemaname.tablename')

-- Function: public.get_gp_create_sql(text)

-- DROP FUNCTION public.get_gp_create_sql(text);

CREATE OR REPLACE FUNCTION public.get_gp_create_sql(tablename text)
  RETURNS text AS
$BODY$
                            try:                                                                                                    
                                  table_name = tablename.lower().split('.')[1]
                                  table_schema = tablename.lower().split('.')[0]                                              
                            except (IndexError):                                  
                                  return 'Please in put "tableschema.table_name" '
                            #get table oid
                            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 :                                 
                                  return ' '
                          
                            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 column name and column type
                                  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);
                                     #return get_columns;
                                  rv_columns = plpy.execute(get_columns)
                          
                            #get distributed key
                                  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']:
                                        #plpy.info(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(']') + ")"
                                        #get_table_distribution2 = "SELECT attname FROM pg_attribute WHERE attrelid = '" + table_oid + "' AND attnum in (" + rv_distribution1[0]['attrnums'] + ")"
                                        #plpy.info(get_table_distribution2)
                                        rv_distribution2 = plpy.execute(get_table_distribution2, 500)                                
                            #get index define
                                  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 partition info    
                                  #get_parinfo1 = "select attname as columnname from pg_attribute where attnum in (select paratts[0] from pg_partition where parrelid=%s) and attrelid=%s;"%(table_oid,table_oid);
                  get_parinfo1 = """
                   SELECT att.attname as columnname,att.attnum,pp.parlevel
                   FROM pg_attribute att,pg_partition pp
                   WHERE att.attnum in (select paratts[0] from pg_partition where parrelid=%s)
                    AND att.attrelid=%s and pp.parrelid=att.attrelid
                    AND att.attnum=pp.paratts[0] ;
                    """%(table_oid,table_oid);
                                  get_parinfo2 ="""
                                  SELECT pp.parrelid,pr1.parchildrelid,pp.parlevel,pr1.parparentrule,pr1.oid,
                                            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(pr1.oid, true) AS partitionboundary
                                   FROM  pg_partition pp, pg_partition_rule pr1
                                   WHERE pp.paristemplate = false AND pp.parrelid = %s AND pr1.paroid = pp.oid
                                   order by pr1.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']:
                        if i['attname'] in ["LIMIT","PARTITION","RIGHT","END","binary","foreign","LOCALTIME"]:
                            i['attname']='"'+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:
                                        #plpy.info(str(rv_reloptions))
                                        create_sql+="with("+str(rv_reloptions).replace("'","").strip('[').strip(']') +")\n"
                                  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'

                              #get partion or subpartion
                  if len(v_par_parent)==1:
                    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)"
                  elif len(v_par_parent)==2:
                    #subpartsql="";
                    v_subpartsql={};
                    for i in v_par_info:
                        if i['parlevel']==1:
                            v_subpartsql[i['parparentrule']]="";

                    for i in v_par_info:
                        if i['parlevel']==0:
                            partitiontype0= i['partitiontype'];
                        elif i['parlevel']==1:
                            partitiontype1= i['partitiontype'];
                            v_subpartsql[i['parparentrule']]+="                  "+i['partitionboundary']+',\n';

                    for i in v_par_info:
                        if i['parlevel']==1:
                            v_subpartsql[i['parparentrule']]=v_subpartsql[i['parparentrule']].strip(',\n');    
            
                    if v_par_parent[0]['parlevel']==0:
                        create_sql+='\nPARTITION BY ' + partitiontype0 + "("+v_par_parent[0]['columnname']+")\n";
                        create_sql+='      SUBPARTITION BY ' + partitiontype1 + "("+v_par_parent[1]['columnname']+")\n";
                        create_sql+="            (\n"
                    elif v_par_parent[1]['parlevel']==0:
                        create_sql+='\nPARTITION BY ' + partitiontype0 + "("+v_par_parent[1]['columnname']+")\n";
                        create_sql+='      SUBPARTITION BY ' + partitiontype1 + "("+v_par_parent[0]['columnname']+")\n";
                        create_sql+="            (\n"
                    for i in v_par_info:
                        if i['parlevel']==0:
                            create_sql+="            "+i['partitionboundary']+'\n';
                            #create_sql+="                  ("+"\n"+subpartsql+"\n"+"                  ),"+"\n";
                            create_sql+="                  ("+"\n"+v_subpartsql[i['oid']]+"\n"+"                  ),"+"\n";
                    create_sql = create_sql.strip(',\n');
                    create_sql+="\n            )"
                                  create_sql += ";\n\n"
                                
                                  for i in rv_index:
                                        create_sql += i['indexdef']+';\n'
                                  create_sql+='\n'   
                            #get comment
                            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)
                            get_column_comment="select 'comment on column %s."%(tablename)+'''"'''+"'||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;"%(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;
$BODY$
  LANGUAGE 'plpythonu' VOLATILE;
ALTER FUNCTION public.get_gp_create_sql(text) OWNER TO odsusr;