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
