Issue 1:
Describe the bug

In order to fix geoalchemy/geoalchemy2#137 I would like to check that indexes are properly created in the unit tests. For this I use from sqlalchemy import inspect but it does not return the functional indexes, only the regular ones (note that the two indexes properly appear in the table.indexes entry). While when I check with pgcli the indexes are properly displayed:

\d gis.test_table_with_indexes
+----------+-------------------+---------------------------------------------------------------------------+
| Column | Type | Modifiers |
|----------+-------------------+---------------------------------------------------------------------------|
| id | integer | not null default nextval('gis.test_table_with_indexes_id_seq'::regclass) |
| test | character varying | |
+----------+-------------------+---------------------------------------------------------------------------+
Indexes:
"test_table_with_indexes_pkey" PRIMARY KEY, btree (id)
"test_functional_index" btree (lower(test::text))
"test_index" btree (test)

I tried several combinations of regular and functional indexes and the functional ones are always missing.

I also note that the primary key index is not displayed, I don't know if it is intended?

To Reproduce

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Index
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

engine = create_engine('postgresql://****************', echo=True)
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)

def test_functional_indexes():
Base = declarative_base(metadata=metadata)

class TestTableWithIndexes(Base):
__tablename__ = 'test_table_with_indexes'
__table_args__ = {'schema': 'gis'}
id = Column(Integer, primary_key=True)
test_col = Column(String)
test_functional_col = Column(String)

Index("test_functional_index", func.lower(TestTableWithIndexes.__table__.c.test_functional_col))
Index("test_index", TestTableWithIndexes.__table__.c.test_col)

TestTableWithIndexes.__table__.drop(engine)
TestTableWithIndexes.__table__.create(engine)

inspector = inspect(engine)
indices = inspector.get_indexes(TestTableWithIndexes.__tablename__)

print(indices) # <= ONLY "test_index" IN THE LIST
assert len(indices) == 2 # FAILS

Error

________________________ test_functional_indexes ________________________

def test_functional_indexes():
Base = declarative_base(metadata=metadata)

class TestTableWithIndexes(Base):
__tablename__ = 'test_table_with_indexes'
__table_args__ = {'schema': 'gis'}
id = Column(Integer, primary_key=True)
test = Column(String)

Index("test_functional_index", func.lower(TestTableWithIndexes.__table__.c.test))
Index("test_index", TestTableWithIndexes.__table__.c.test)

TestTableWithIndexes.__table__.drop(engine)
TestTableWithIndexes.__table__.create(engine)

inspector = get_inspector(engine)
indices = inspector.get_indexes(TestTableWithIndexes.__tablename__)

print(indices)
> assert len(indices) == 2
E AssertionError: assert 1 == 2
E + where 1 = len([{'column_names': ['test'], 'include_columns': [], 'name': 'test_index', 'unique': False}])

tests/test_functional_index.py:36: AssertionError

Versions

OS: Linux

Python: 3.8.10

SQLAlchemy: 1.4.29

Database: PostgreSQL 12.9

DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg2==2.9.3

Additional context

No response

Issue 2:
Describe the use case

We are using SQLAlchemy to work with Postgres DB that has full-text indexes (to_tsvector and so on), and whenever those need a change it is unfortunate that alembic is unable to generate the automated diff. One reason for that is that postgres dialect currently does not support reflecting expression-based indexes.

It is possible to live without autogenerated migrations, but we had cases when a schema change has been missed or a typo in the index expression slipped by.

Databases / Backends / Drivers targeted

Postgres (any drivers)

Example Use

Create table with an expression-based index

create table t123123 (x varchar, y varchar);
create index t123123idx_xlowery on t123123 ((x || lower(y)));

Reflect the index from the live table:

import sqlalchemy as sa
engine = sa.create_engine('postgresql://scott:tiger@172.17.0.1:5432/test')
meta = sa.MetaData()
user_table = sa.Table('t123123', meta)
insp = sa.inspect(engine)
insp.reflect_table(user_table, None)

# Current implementation will fail on the next line, because t123123idx_xlowery index is not reflected
xlowery_idx = [i for i in user_table.indexes if i.name == 't123123idx_xlowery'][0]
print(str(sa.schema.CreateIndex(xlowery_idx).compile()))
# CREATE INDEX t123123idx_xlowery ON t123123 (CAST(x AS TEXT) || lower(CAST(y AS TEXT)))

Additional context

Adding pg_get_expr(ix.indexprs, ix.indrelid) to the list of index metadata requested in sa.dialects.postgres.base.PGDialect.get_indexes should be straightforward. Also, there are parsers for Postgres code (https://github.com/pganalyze/libpg_query and derivatives) that should make the process of converting Postgres code into SA expression objects easier.