|
Hello,
W dniu 2012-08-29 23:06, Merlin Moncure pisze: Well, the only reason what you're trying to do works at all is because the database isn't stricter about double checking to see if your stuff is IMMUTABLE: it isn't, so of course it doesn't work. How about a trigger on the child table that updates an indexed column on parent? merlin According to docs: "An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever." My tables look like this: CREATE TABLE groups ( id serial PRIMARY KEY, last_item integer REFERENCES items ) WITHOUT OIDS; CREATE TABLE items ( id serial PRIMARY KEY, group integer NOT NULL REFERENCES groups, ts timestamp DEFAULT now() ) WITHOUT OIDS; The index: CREATE INDEX groups_last_ts ON groups USING btree (items_ts(last_post)); Plpgsql function items_ts returns timestamp for given item, which will never change(that's my assumption), so in fact according to definition IT IS immutable fuction. Unfortunately, whenever I update last_item column in groups, I get wrong results, so I query like this: SELECT * FROM groups WHERE items_ts(last_item) > now() - interval '1 week' returns "outdated" results I do realize about other ways for solving this problem, however I would prefer if it worked in the way described above. Thanks! -- Regards, Grzegorz |
