Hi all -
I'm trying to add functional index support to Rails' Active Record and
am getting stuck when it comes to a method Rails has to print out the
indexes associated with a given table.
The SQL being run is below:
SELECT distinct i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'employers'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
('public') )
AND a.attrelid = t.oid
AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
ORDER BY i.relname;
This returns the following:
relname | indisunique | attname
--------------------------------------+-------------+---------
foo_idx | f | name
foo_idx | f | url
index_employers_on_name | f | name
But that doesn't show a functional index I created on the employers
table. This does:
careers_development=# select indexname, indexdef from pg_indexes where
tablename = 'employers';
indexname | indexdef
-------------------------+-----------------------------------------------------------------------------------
employers_pkey | CREATE UNIQUE INDEX
employers_pkey ON employers USING btree (id)
index_employers_on_name | CREATE INDEX index_employers_on_name ON
employers USING btree (name)
index_employers_on_url | CREATE INDEX index_employers_on_url ON
employers USING btree (lower((url)::text))
foo_idx | CREATE INDEX foo_idx ON
employers USING btree (name, url)
I don't know enough about PG's internals to quite know what I need to
query on, but what I'd like is to have a query that returns the first
result set with the following addition:
relname | indisunique | attname
--------------------------------------+-------------+---------
index_employers_on_url f lower(url)
Is there anyway to do that beyond parsing the CREATE INDEX string?
Some way to modify that first query to include the functional index
and somehow get the function part of it into that last column?
Appreciate any pointers any of you might have on this.
Thanks!
