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
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? |