Re: Stored procedure returning row or resultset

If your f1, f2, and f3 functions depend only on their arguments; that
is, if you call one
of those functions with the same set of arguments, it will return the
same answer every
time, you can define the functions as "iscachable" as follows:

create function f1()
...
language ...
with (iscachable);

That way, using your SELECT statement, f1 would only be computed once.
Furthermore,
if you call it a while later with the same arguments, it might not be
computed at all.

Heiko Stoermer wrote:

>Hello,
>
>I'm trying to achieve the following with pl/pgsql in postgres 7.2:
>
>I need a stored procedure proc() that calculates values for some fields
>a = f1()
>b = a + f2()
>c = b + f3()
>and returns the tuple (a,b,c) as a rowtype,
>
>so that "select proc()" produces a "normal" resultset that can be used in an
>application.
>
>
>I know of course that I could write out
>SELECT
>(f1()) AS a
>(f1() + f()) AS b
>(f1() + f2() + f3()) AS c ...
>in plain SQL and create a view on that, but the problem is that f1(), f2(),
>f3() are complex and expensive computations that I want to run only once. In
>the example I would call f1() three times, which is inacceptable.
>
>Is there a way to accomplish this? I have tried around quite a bit, but I
>cannot find out the correct syntax .
>
>Thank you,
>Heiko Stoermer
>
>
>