Re: Stored procedure returning row or resultset

On Monday 14 Oct 2002 3:22 pm, 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,

Move to 7.3beta and use table functions, or look at returning a cursor from a
function. See the online docs for details.

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

Have you looked at marking f1() etc cachable? This means Postgresql will only
call the function once for each parameter-set. Only works for functions like:

square_root(number)

and not

next_random_number()

--  Richard Huxton