Thread: functions: stable/volatile

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

On Mon, 20 Nov 2006, Rafal Pietrak wrote:

> 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.

-- Not tested

create function update_my_table(newvalue, input)
    returns void
    volatile
    language plpgsql
as $$
declare
    selector selector_field_type;
begin
    selector := my_function(input);
    update my_table set filed=newvalue
        where my_table.selector_field=selector;
end;
$$;

-- and then

select update_my_table(newvalue, input);

-- Not tested.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

Hmmm. Definitly better then my code.
1. does not use the spurious tool_table
2. is surely safe on high traffic and against unintencialnal tool_table
littering.

yet, forces us to write down "SELECT" when we actually mean "UPDATE".
This is sort of 'source code obfuscation' - it's not a very good
practice, but only an aestetics issue. I can live with that.

So, thenx for the hint. I'll surely use it.

-R


On Mon, 2006-11-20 at 16:59 +0100, Tomasz Ostrowski wrote:
> On Mon, 20 Nov 2006, Rafal Pietrak wrote:
>
> > 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.
>
> -- Not tested
>
> create function update_my_table(newvalue, input)
>     returns void
>     volatile
>     language plpgsql
> as $$
> declare
>     selector selector_field_type;
> begin
>     selector := my_function(input);
>     update my_table set filed=newvalue
>         where my_table.selector_field=selector;
> end;
> $$;
>
> -- and then
>
> select update_my_table(newvalue, input);
>
> -- Not tested.
>
> Regards
> Tometzky