def get_oids(conn): cur = conn.cursor() cur.execute(""" SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname != 'pg_catalog' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; """) for oid, namespace, relname in cur: obj = Struct() obj.oid = oid obj.namespace = namespace obj.name = relname yield obj
def get_table_parents(conn,obj): """ Obtiene las tablas padres de una tabla (tablas de las que hereda una tabla) """ cur.execute(""" SELECT c.oid::pg_catalog.regclass, c.relname, n.nspname FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_inherits i ON c.oid=i.inhparent LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE i.inhrelid = %s ORDER BY inhseqno """, [obj.oid]) for oid, relname, namespace in cur: obj = Struct() obj.oid = oid obj.namespace = namespace obj.name = relname yield obj
def get_relname_oid(conn, relname): """ Obtiene el OID de una relación (tabla, indice, pkey, etc) """ cur = conn.cursor() cur.execute(""" SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = %s AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; """, [relname]) for oid, namespace, relname in cur: obj = Struct() obj.oid = oid obj.namespace = namespace obj.name = relname return obj
def get_table_indexes(conn,obj): """ Obtiene los indices de una tabla """ cur = conn.cursor() cur.execute(""" SELECT c2.oid, c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = %s AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname """, [obj.oid]) for oid, name, isprimary, isunique, isclustered, isvalid, sql_definition, tablespace_oid in cur: index = Struct() index.oid = oid index.name = name index.isprimary = isprimary index.isunique = isunique index.isclustered = isclustered index.isvalid = isvalid index.sql_definition = sql_definition index.tablespace_oid = tablespace_oid index.columns = get_index_columns(conn, index) yield index