Changeset 720 for trunk/pg.py


Ignore:
Timestamp:
Jan 12, 2016, 7:44:49 AM (4 years ago)
Author:
cito
Message:

Simplify queries by using alias names

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/pg.py

    r719 r720  
    587587            # if not found, check again in case it was added after we started
    588588            self._pkeys = {}
    589             for r in self.db.query(
    590                 "SELECT pg_namespace.nspname, pg_class.relname,"
    591                     " pg_attribute.attname FROM pg_class"
    592                 " JOIN pg_namespace"
    593                     " ON pg_namespace.oid = pg_class.relnamespace"
    594                     " AND pg_namespace.nspname"
    595                     " NOT SIMILAR TO 'pg/_%|information/_schema' ESCAPE '/'"
    596                 " JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid"
    597                     " AND NOT pg_attribute.attisdropped"
    598                 " JOIN pg_index ON pg_index.indrelid = pg_class.oid"
    599                     " AND pg_index.indisprimary"
    600                     " AND pg_attribute.attnum"
    601                         " = ANY (pg_index.indkey)").getresult():
     589            q = ("SELECT s.nspname, r.relname, a.attname"
     590                " FROM pg_class r"
     591                " JOIN pg_namespace s ON s.oid = r.relnamespace"
     592                " AND s.nspname NOT SIMILAR"
     593                " TO 'pg/_%|information/_schema' ESCAPE '/'"
     594                " JOIN pg_attribute a ON a.attrelid = r.oid"
     595                " AND NOT a.attisdropped"
     596                " JOIN pg_index i ON i.indrelid = r.oid"
     597                " AND i.indisprimary AND a.attnum = ANY (i.indkey)")
     598            for r in self.db.query(q).getresult():
    602599                cl, pkey = _join_parts(r[:2]), r[2]
    603600                self._pkeys.setdefault(cl, []).append(pkey)
     
    623620
    624621        """
    625         where = "pg_class.relkind IN (%s) AND" % ','.join(
    626             ["'%s'" % x for x in kinds]) if kinds else ''
    627         return [_join_parts(x) for x in self.db.query(
    628             "SELECT pg_namespace.nspname, pg_class.relname"
    629             " FROM pg_class "
    630             " JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace "
    631             " WHERE %s pg_namespace.nspname"
    632             " NOT SIMILAR TO 'pg/_%%|information/_schema' ESCAPE '/'"
     622        where = " AND r.relkind IN (%s)" % ','.join(
     623            ["'%s'" % k for k in kinds]) if kinds else ''
     624        return [_join_parts(r) for r in self.db.query(
     625            "SELECT s.nspname, r.relname"
     626            " FROM pg_class r"
     627            " JOIN pg_namespace s ON s.oid = r.relnamespace"
     628            " WHERE s.nspname NOT SIMILAR"
     629            " TO 'pg/_%%|information/_schema' ESCAPE '/' %s"
    633630            " ORDER BY 1, 2" % where).getresult()]
    634631
     
    662659            raise _prg_error('Class %s does not exist' % qcl)
    663660
    664         q = "SELECT pg_attribute.attname, pg_type.typname"
    665         if self._regtypes:
    666             q += "::regtype"
    667         q += (" FROM pg_class"
    668             " JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid"
    669             " JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid"
    670             " JOIN pg_type ON pg_type.oid = pg_attribute.atttypid"
    671             " WHERE pg_namespace.nspname = '%s' AND pg_class.relname = '%s'"
    672             " AND (pg_attribute.attnum > 0 OR pg_attribute.attname = 'oid')"
    673             " AND NOT pg_attribute.attisdropped") % cl
     661        q = ("SELECT a.attname, t.typname%s"
     662            " FROM pg_class r"
     663            " JOIN pg_namespace s ON r.relnamespace = s.oid"
     664            " JOIN pg_attribute a ON a.attrelid = r.oid"
     665            " JOIN pg_type t ON t.oid = a.atttypid"
     666            " WHERE s.nspname = '%s' AND r.relname = '%s'"
     667            " AND (a.attnum > 0 OR a.attname = 'oid')"
     668            " AND NOT a.attisdropped") % (
     669                '::regtype' if self._regtypes else '', cl[0], cl[1])
    674670        q = self.db.query(q).getresult()
    675671
Note: See TracChangeset for help on using the changeset viewer.