Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Heiko Stoermer <heiko.stoermer@login-solutions.de>,
	pgsql-sql@postgresql.org
Subject: Re: Stored procedure returning row or resultset
Date: Mon, 14 Oct 2002 16:05:03 +0100
User-Agent: KMail/1.4.3
References: <200210141622.54638.heiko.stoermer@login-solutions.de>
In-Reply-To: <200210141622.54638.heiko.stoermer@login-solutions.de>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210141605.04208.dev@archonet.com>

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 =3D f1()
> b =3D a + f2()
> c =3D 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=20
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 on=
ly=20
call the function once for each parameter-set. Only works for functions lik=
e:

square_root(number)

and not

next_random_number()

--=20
  Richard Huxton