From: Brian Blaha <bblaha@umr.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Stored procedure returning row or resultset
Date: Mon, 14 Oct 2002 15:15:04 +0000
Message-ID: <3DAADF78.9080200@umr.edu> (raw)
References: <200210141622.54638.heiko.stoermer@login-solutions.de>
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
>
>
>
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: bblaha@umr.edu
Subject: Re: Stored procedure returning row or resultset
In-Reply-To: <3DAADF78.9080200@umr.edu>
* 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