Hi,
Some time ago I've asked here for some guidence on the use of
stable/volative/etc function declaration. From what I've learned, I have
created a piece of code which I'm not very proud of. But may be I
understand it all wrong, so here is my problmem at hand:
I have a function, which is:
a) computationally intensive,
b) it's output is used to select *just*one* row of a fairly large
table.
c) it should not be called more then once per SELECT/UPDATE statement,
since calling it another time will result in a different return-string,
which is not harmfull, but not desirable either.
d) it updates another table in the database (makes side effects, which
again: is not harmfull, but highly undesidable).
Initially, I tried to define this function as STABLE, but after
consulting this list before, I know, that I shouldn't have done that.
The most obvious construct I'd use is:
UPDATE my_table set filed=newvalue WHERE
my_function(input)=my_table.selector_field;
but it's useless, since the function would have been be evaluated for
every row of the table.
My intention is to have it evaluated just once.
So I've created a VIEW:
CREATE VIEW my_view AS SELECT * from my_table;
And I've created another table:
CREATE TABLE my_tool_table (selector text, value text);
which I use inside an UPDATE RULE for my_vew:
CREATE VIEW refresh AS ON UPDATE to my_vew DO INSTEAD (INSERT INTO
my_tool_table (selector, value) SELECT (my_function(), new.value);
UPDATE my_table SET my_table.field=tt.value FROM my_tool_table tt WHERE
my_table.selector=tt.selector; DELETE FROM my_tool_table);
Which sort of works (this is the semantics I actually need), but:
1. it works until somebody accidenty litters the my_tool_table.
2. I'm not quite sure if it's transaction safe - if even on high
traffic, it keeps the contents of my_tool_table at the "at most one row
of data" level. The row existance confined to inserted/deleted within
the "refresh" VIEW.
3. I could live with the above code, provided I could INSERT INTO
TEMPORARY my_tool_table - but obviosly, whithin the RULE I cannot create
temporary tables.
The most desirable solution would be if I could tell the RDBMS at the
function definition time, that: "Although (because of) this function has
side effects, because it's not stable: you (the RDMBS) may call it just
*once* per executed statement and you *must* use that single output for
every row you happen to visit during this statement".
In other words, this function is NOT stable, but I'd like the engine to
use it AS stable. Is there a function declaration construct, that I
could use to pass this sort of information to the engine?
But, even if there is no way to tell the engine such thing, my real
question really is: Is there a cleaner way to implement this
functionality?
Thenx
-R
