Hello Postgressers, I am using table inheritance and have e.g. the following tables:
create table animal ( ... ); create table dog ( ... ) inherits (animal); create table cat ( ... ) inherits (animal); create table person ( ... ) inherits (animal); create table musician ( ... ) inherits (person); create table politician ( ... ) inherits (person); Now I have a query that gets all the "animal"'s except for those that are "person"'s. select * from only animal won't cut it, because it leaves out the dogs and cats. select *, tableoid::regclass relname from animal where relname != 'person'::regclass also won't cut it because it leaves out the musicians and politicians. So I have created an immutable function is_a_kind_of(tbl regclass, parent_tbl regclass) that returns true iff tbl is identical with, or directly or indirectly inherits from, parent_tbl. For example: is_a_kind_of('person','person') => true is_a_kind_of('person','animal') => true is_a_kind_of('musician','person') => true is_a_kind_of('animal','person') => false is_a_kind_of('dog','person') => false No problems so far. Now my query works: select *,tableoid from "animal" where not is_a_kind_of(tableoid::regclass::text, 'person') This query is somewhat slow though - I'd like to index the is_a_kind_of() call. And Postgres supports functional indexes! So I try: create index animal_is_person on animal ( is_a_kind_of(tableoid::regclass, 'person') ); ERROR: index creation on system columns is not supported I see that this is because "tableoid" is a system column. Does anyone know any workaround for this? So close yet so far away! Thanks! Ryan