From: Richard Huxton <dev@archonet.com>
To: Heiko Stoermer <heiko.stoermer@login-solutions.de>
To: pgsql-sql@postgresql.org
Subject: Re: Stored procedure returning row or resultset
Date: Mon, 14 Oct 2002 16:05:03 +0100
Message-ID: <200210141605.04208.dev@archonet.com> (raw)
In-Reply-To: <200210141622.54638.heiko.stoermer@login-solutions.de>
References: <200210141622.54638.heiko.stoermer@login-solutions.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, heiko.stoermer@login-solutions.de
Subject: Re: Stored procedure returning row or resultset
In-Reply-To: <200210141605.04208.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox