Query rewriting: updates
От | Richard Huxton |
---|---|
Тема | Query rewriting: updates |
Дата | |
Msg-id | 000b01c0bb88$c65e7780$1001a8c0@archonet.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: