Tom Lane wrote:
David Rysdam <drysdam@xxxxxxxxxx> writes:Well, since I can't have more than one index of a given name in a schema anyway, I'll have to name them "$tablename_$indexname" or something, which means I won't have to verify they are on a particular table.
Why can't I have the same index name be on different tables?
You can ... if they are in different schemas. Indexes and tables share the same namespace, ie, they must be unique within a schema.
As for your original question, you probably want something like
SELECT ... FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND relname = 'indexname' AND nspname = 'schemaname' AND relkind = 'i';
If you actually want to verify that this index is on a specific table, you'll need a more complicated join involving pg_index and a second scan of pg_class. See http://www.postgresql.org/docs/8.0/static/catalogs.html
regards, tom lane
Anyway, this query looks good. I was getting lost in all the terminology ("namespace" vs "schema") data distributed all over (some stuff in pg_index, some in pg_class, etc).
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
