Is there a way to create a functional index that tables tableoid column as an arg?

From: Ryan Murphy <ryanfmurphy(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is there a way to create a functional index that tables tableoid column as an arg?
Date: 2018-03-10 18:53:24
Message-ID: CAHeEsBejhpikWu+hEfsiiXKR0kCAnmgmuFWG8C_oQjxQ9D62=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message legrand legrand 2018-03-10 18:53:37 Re: wrong message when trying to create an already existing index
Previous Message Bruce Momjian 2018-03-10 17:27:04 Re: momjian.us is down?