Greenplum常用SQL——通過表名查詢列名、類型、是否具有序列

"""select nt.nspname as table_schema,
         c.relname as table_name,
         a.attname as column_name,
         a.attnum as ordinal_position,
         format_type(a.atttypid, a.atttypmod) as data_type,
         c.relkind = 'r' AS is_updatable,
         a.atttypid in (23, 20) and a.atthasdef and
             (select position ( 'nextval(' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0 and
                          position ( '::regclass)' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0
              FROM pg_catalog.pg_attrdef d
              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as has_sequence
          from pg_catalog.pg_class c join pg_catalog.pg_namespace nt on (c.relnamespace = nt.oid)
             join pg_attribute a on (a.attrelid = c.oid)
         where c.relname = '%s' and nt.nspname = '%s'
         and a.attnum > 0 and a.attisdropped = 'f'
         order by a.attnum """ % (quote_unident(self.table), quote_unident(self.schema))      

下面是上述sql查詢出來的資訊

名	字段	解釋
table_schema	pg_catalog.pg_namespace.nspname	schema
table_name	pg_catalog.pg_class.relname
column_name	pg_attribute.attname	列名
ordinal_position	pg_attribute.attnum
data_type	format_type(pg_attribute.atttypid, pg_attribute.atttypmod)	列的資料類型
is_updatable	pg_catalog.pg_class.relkind = ‘r’	是否可更新，也就是relation

.atttypid in (23, 20) and a.atthasdef and
             (select position ( 'nextval(' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0 and
                          position ( '::regclass)' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0
              FROM pg_catalog.pg_attrdef d
              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as      

= self.db.query(queryString.encode('utf-8')).dictresult()
        while count < len(resultList):
            row = resultList[count]
            count += 1
            ct = unicode(row['data_type'])
            if ct == 'bigserial':
               ct = 'bigint'
            elif ct == 'serial':
               ct = 'int4'
            name = unicode(row['column_name'], 'utf-8')
            name = quote_ident(name)
            if unicode(row['has_sequence']) != unicode('f'):
                has_seq = True
            else:
                has_seq = False