Re: Defer a functional index calculation?

[Randall Lucas <rlucas@xxxxxxxxxxx>]

On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason <sam@xxxxxxxxxxxxx> 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