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
