On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason <sam@samason.me.uk> wrote:
On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote: > I added a functional index. > > create table example (id serial primary key, stuff text, parent_id int); > create index example_root_idx on example (get_root_id(id)); > > (get_root_id(id) pulls an example row and recurses onto parent_id until it > hits a root)
I don't think you can do that; are you lying in the function's definition that it's "immutable"? As far as I know, and a quick check
Busted! Yes, I was lying to postgres. (The function is all-but-immutable, your honor; I was only trying lazily to memoize its output...)
What about having some trigger to "cache" the entry's root "parent_id" in another column?
