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?
Looks like that's what I'm headed for.
Thank you,
Randall