How to get the full CREATE INDEX clause for an existing index in pure SQL?

Question

I wrote a simple script to reindex our PG databases once a week. It has simple logic:

• get top 10 indexes by number of bloated bytes
• lookup create an index clause from a hashmap variable (index name -> create clause)
  • if not, enter stderr and go to the next index
• create a new index at the same time
• delete old index at the same time
• rename new index

And being a proper lazy developer, I don't like the concept that I have to constantly update my hash file. (On the other hand, I found two ineffective indicators in this case).

pg_index seems pretty informative, is there a way to restore the create constructor from it?

It's easy to get a list of columns, but we use different types of indexes, different opclasses for fields, private indexes ... And who knows what. It is important to make sure that we get the same offer that was used to create the index in the first place.

Answer 1

Yes, there is a built-in function pg_get_indexdef for this purpose.

eg:.

 regress=> SELECT pg_get_indexdef('demo_pkey'::regclass); pg_get_indexdef -------------------------------------------------------- CREATE UNIQUE INDEX demo_pkey ON demo USING btree (id) (1 row)