[DBIx-Class-Devel] [dbsrgits/sql-translator] Support for Postgres opclass on fields of an index (Issue #154)

[M Conrad]

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](https://www.postgresql.org/docs/current/pgtrgm.html) :

  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);


-- 
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/sql-translator/issues/154
You are receiving this because you are subscribed to this thread.

Message ID: <dbsrgits/sql-translator/issues/154 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20221221/39c8b903/attachment.htm>