Re: Defer a functional index calculation?

On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason <sam(at)samason(dot)me(dot)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