From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Heiko Stoermer <heiko(dot)stoermer(at)login-solutions(dot)de>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Stored procedure returning row or resultset |
Date: | 2002-10-14 15:05:03 |
Message-ID: | 200210141605.04208.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Blaha | 2002-10-14 15:15:04 | Re: Stored procedure returning row or resultset |
Previous Message | Heiko Stoermer | 2002-10-14 14:22:54 | Stored procedure returning row or resultset |