Patch to support PostgreSQL functional indexes.

Nice. But I think you should explicitly document that PostgreSQL supports functional indexes while MySQL doesn't, so that people don't have to Google that information themselves.

I like this, or something like it, for post 2.2.

You've changed the schema info query for postgresql though, why is that?

Michael, looks like the query is changed so it would return the definition of the function so it would be included in the schema dump, not sure why the select conditions was changed though.

I will admit to not being a postgresql expert and understanding all the changes in that query. I asked on the PG mailing list for what I wanted (the SQL to return both simple and functional indexes -- specifically the function itself) and what query I currently had.

Here's the post here:

http://archives.postgresql.org/p...

Was hoping there would be some other postgres folks here that could confirm that :)

In the tests I setup the query does do what it should as far as I can tell...

+1

This would make my life so much easier!

I'd suggest using :expression or :function rather than :functional in #create_index.

+1 have had a couple of cases where this would have been useful, end up dropping into SQL in the migration to handle it.

Code looks good and all tests pass on edge against PG 8.3.4

I checked that the pg_get_indexdef() function needed by this patch is available in older PostgreSQL versions -- there's no note in the usually-thorough PG docs to indicate otherwise, so I imagine it's safe for at least all 8.x releases.

@mikel: I have no problem with dropping into SQL for this, but the reason I'd really like to see this patch integrated is that it will cause functional indexes to get copied to the test database too, 'cos I want to write a full-text-search plugin that reflects on functional indexes.

It's a general problem that only a subset of a DB schema is automatically cloned to the test database. Setting dump_format to :sql sucks because it breaks fixture loading horribly once you have any foreign key constraints in your DB.)

For that reason I'd be inclined to submit a patch that also dumps view definitions to schema.rb... but...

Perhaps an alternative for us PostgreSQL users would be to make :sql dumps and fixtures play nicely together, e.g. by providing database adaptor methods to disable and enable constraints. Just a thought.

@Steve: I haven't tried it, but doesn't rake db:test:clone_structure work on the functional indicies?

I use that all the time on my app because I use views and triggers on some legacy code.

Mikel

@Mikel - that works like the :sql dump format, and does indeed clone the functional indexes, not just the views and triggers.

Of course the problem is then that the foreign keys will prevent fixtures from loading properly in the test database.

@Koz - yes, that was an uninformed/confused comment on my part. I have since had the happy experience of :sql dumps Just Working for me.

And in this case, then using :sql dumps is likely a better option than adding support for functional indexes. At a certain point, you reasonably have to expect to drop into SQL in your migrations.

I don't want to argue too much, but I'm not sure I understand why this wouldn't be a useful addition? Following Steve's logic above, couldn't one say that we don't need any Rails/migration magic because all of it can be done in SQL?

I realize that right now this only supports creating functional indexes in PostgreSQL, but it paves the way for someone to add Oracle/Sybase/etc functionality more easily later on.

Functional indexes can be pretty handy, particularly when doing things like "show me all the foobar's starting with the letter 'A'".

Anyway, my last 2 cents on the issue :)

I just think it's not quite worth it if it'll only be supported in one database that we ship out of the box. However if a plugin along those lines gets popular with other DBs. Then we can revisit the decision