I can do the following, but I wondered if there is a more efficient way to
do this (in version 7.1 will be fine). The particular set of updates I need
to do are pretty time-critical. The situation boils down to...
I have a table foo with a session field:
create table foo (a int, b text, session int);
and a session table with the value to be used:
create table foo_sess (sessname text, s int);
I also have a function sessval() that does:
SELECT s FROM foo_sess WHERE sessname=$1 LIMIT 1 INTO curr;
RETURN curr;
I've tagged it "iscachable" so it should only be evaluated once per
query-plan (if I've understood that bit of the docs).
Now, for inserts I can just use a "default sessval()" in the table
definition, which should be about as efficient as I can get.
For updates, I'd like to use a rule but can't because there's a loop on
rewriting updates to the same table as the rule is on. Alternatively, I'd
like to use a trigger defined as FOR EACH STATEMENT but that's not
implemented yet.
So - my options seem to be:
1. Use triggers and accept the extra work of updating each row separately
and possibly evaluating sessval() many times (need to check what happens
with this)
2. Replace references to "foo" with "foo_view" in my application and use
rules.
3. Alter queries in my application and make sure I don't forget to set
"session" anywhere (hmm...)
Have I missed a gee-whiz stroke of genius somewhere here?
TIA
- Richard Huxton