Let’s say you’ve built an automated system that builds out your database (common with ORM tools), which includes building indexes with ‘where’ clauses, like so:<\/p>\n
\ncreate index index_test \non test (col1, col2, col3) \nwhere col4 = 'test';\n<\/pre>\nIf you were to build an automated migration for this database, it’s helpful to know if this index will differ in the next version. Assuming a ‘duck type’ style equality, you’d want to verify the columns and their types are the same, as well as the where clause – if any of these differ, you need to rebuild the index. The problem is that a developer who writes the where clause may not format it exactly as the database does (they are only writing the ‘where’ bit), so it would be helpful to be able to parse the SQL, the re-constitute it through Postgres to check if there are material differences.<\/p>\n
If you have created an index with a WHERE clause, you can query it back like so:<\/p>\n
\nselect pg_get_expr(indpred, indrelid), *\nFROM pg_index AS idx\nJOIN pg_class AS idx_cls ON idx_cls.oid = idx.indexrelid\nWHERE pg_class.relname = 'index_test'\n<\/pre>\nInternally, there are several tables that expose data of type pg_node_tree (a parse tree), such as index where clauses, and rewrite rules. The pg_get_expr function turns these back into SQL, given the OID of a type, which works great for reconstructing indexes. There is a third argument to this function to “pretty print” the output, but it seems to just reduce the amount of parentheses included.<\/p>\n