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

Reply via email to