Greenplum6获取表结构

python语法结构。。。

--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 distkey from pg_catalog.gp_distribution_policy t where localoid = %s " %(table_oid)
    rv_distribution1 = plpy.execute(get_table_distribution1,500)
    rv_distribution2 = ''
    if rv_distribution1 and rv_distribution1[0]['distkey']:
    	get_table_distribution2 = "select attname from pg_attribute where attrelid= %s and attnum in ("%(table_oid) + str(rv_distribution1[0]['distkey']).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);
    v_par_parent = plpy.execute(get_parinfo1);              
    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_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;

使用函数：

    SELECT get_table_structure('public.testtable');

结果：

get_table_structure                                                                 |
------------------------------------------------------------------------------------+
create table public.testtable ( id   integer )Distributed by (id);