On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason <s...@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? > Looks like that's what I'm headed for. Thank you, Randall