"Philip Hallstrom" <[EMAIL PROTECTED]> writes: > 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; Well, the problem with this is that it only considers simple index keys, ie, not indexed expressions. The multi-argument version of pg_get_indexdef() would probably help. Something like SELECT i.relname, d.indisunique, pg_get_indexdef(i.oid, a.attnum, false) 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 = i.oid ORDER BY i.relname; Note I've flipped the meaning of the "a" table to be attributes of the index not of the table; this is a handy way to get all the attnum values (index column numbers) we need to pass to pg_get_indexdef(). BTW, why are you suppressing primary keys? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general