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 of the manual[1] seems to confirm it, all functions used in functional indexes must be immutable and the execution of an immutable function can't depend on any data in the database. If you're creating this function as "immutable" then this is lying to the database and it's guaranteed to go wrong at some point (as you seem to be finding out). What about having some trigger to "cache" the entry's root "parent_id" in another column? -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/sql-createindex.html -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
