Add postgresl.regclass type for casting tablenames to oids

Issue #4160 resolved
Sebastian Bank
created an issue

Postgresql provides the regclass type to simplify system catalog queries (reflection):

The regclass input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically. Similarly, casting a table's OID to regclass is handy for symbolic display of a numeric OID.

I think it would be nice (and not too hard) to provide this in sqlalchemy. Maybe like this:

In [1]: import sqlalchemy as sa
   ...: engine = sa.create_engine('postgresql://postgres@')
   ...: class regclass(sa.types.UserDefinedType):
   ...:     def get_col_spec(self):
   ...:         return 'regclass'
   ...: pga = sa.table('pg_attribute',
   ...:                *map(sa.column, ['attrelid', 'attname', 'attnotnull']))
   ...: query =\
   ...:    .where(pga.c.attrelid == sa.cast('pg_class', regclass))\
   ...:    .where(~pga.c.attnotnull)
   ...: engine.execute(query).fetchall()
Out[1]: [(1259, 'relacl', False), (1259, 'reloptions', False)]

In [2]: engine.scalar([sa.cast(1259, regclass)]))
Out[2]: 'pg_class'

Comments (4)

  1. Michael Bayer repo owner
    • changed milestone to 1.3

    I can accept a PR for this into 1.2, if it also has a test that does a simple reflection query like the one above and verifies round trip behavior in test/dialect/postgresql/

  2. Log in to comment