Support for Postgres opclass on fields of an index

[nrdvana]

Postgres has a weird feature for its indexes where you specify an "opclass" on the fields of the index definition. SQL::Translator currently doesn't have a place to store this information, in addition to not being able to round-trip for it.

Here's an example from the trigram module :

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

I now have two projects using trigram indexes, so the itch to fix it is growing. I discovered that the DDL generator already has a special case to not quote field names with parentheses in them, so I was able to work around the problem for generating DDL with:

->add_index({
  name => 'trgm_idx',
  fields => [ '(t) gin_trgm_ops' ],
  options => { using => "GIN" }
})

because Postgres allows arbitrary parentheses around the field name.

It seems a bit hacky. In most other places of DBIC when we want literal SQL we can use a scalar ref. Would that be the right thing to do here?

The next question is how to round-trip this. If I add Postgres Parser support for detecting trigram indices, should I construct index objects like above? (with the parentheses around the column name) or should there be a new scalar-ref feature first and then use that? On the same topic, I don't see a good way to put the "ASC" or "DESC" flags on the fields either, such as used in

CREATE INDEX IF NOT EXISTS x ON y (a DESC, b DESC, c ASC);

As a final consideration, it might be counter-productive to add SQL into the fields because code that wants to introspect a table to find out which columns are indexed would not find a match between sql fields and column names. Maybe there should be field objects that stringify to the field name and contain more descriptive attributes to generate the sql?

[rabbiveesh]

coming back to this, we actually now have the machinery in place to support this!
The question is getting it to also work on the parsing side; that'll take some effort