citext registration

register-citext

def register_citext_type(conn, con_record):
    from psycopg2.extensions import new_type, register_type
    from contextlib import closing
    def cast_citext(in_str, cursor):
        if in_str == None:
            return None
        return unicode(in_str, cursor.connection.encoding)
    citext_oid = None
    with closing(conn.cursor()) as c:
        c.execute("SELECT pg_type.oid FROM pg_type WHERE typname = 'citext'")
        citext_oid = c.fetchone()
    conn.rollback()#very important ;)
    if citext_oid is not None:
        citext_type = new_type(citext_oid, "CITEXT", cast_citext)
        register_type(citext_type)
        
def wrap_engine(engine):
    if (engine.driver == 'psycopg2'):
        listen(engine, "first_connect", register_citext_type)
    return engine
    
# Register CIText to SQLAlchemy's Postgres reflection subsystem.
def open_postgre_database(project_name, pool_size = 10):
    url = get_postgres_url(project_name)
    engine =  wrap_engine(sqlalchemy.create_engine(url,pool_size=pool_size))
    return engine