Greenplum常用SQL——通过表名查询列名、类型、是否具有序列

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查询出来的信息

如下查询是否含有序列

.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

在生成外部表时，需要对数据类型做特殊处理，将bigserial改为bigint，将serial改为int4，以及has_sequence需要特殊处理

= 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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。