Thread: Stored procedure returning row or resultset

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

-- 
Heiko Stoermer
Diplom-Informatiker (FH)             Login & Solutions AG
Tel.: +49-821-2488-0                 http://www.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
>
>  
>

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

Richard Huxton <dev@archonet.com> writes:
> Have you looked at marking f1() etc cachable? This means Postgresql
> will only call the function once for each parameter-set.

Unfortunately that's not true at all, or at least not helpful for this
problem.  The cachable attribute was poorly named, because it leads
people to think that PG *will* cache function results, as opposed to
*could* cache function results.

A possible workaround is along the lines of

SELECT f1, f1 + f2, f1 + f2 + f3 FROM
(SELECT f1() as f1, f2() as f2, f3() as f3 LIMIT 1) tmp;

Note the LIMIT 1 ... without that, the planner may flatten the two
levels of SELECT together, eliminating the savings you're trying for.
(I don't recall offhand all the conditions that govern flattening
of a sub-select, but I'm pretty sure a sub-LIMIT will prevent it.)
        regards, tom lane

On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Have you looked at marking f1() etc cachable? This means Postgresql
> > will only call the function once for each parameter-set.
>
> Unfortunately that's not true at all, or at least not helpful for this
> problem.  The cachable attribute was poorly named, because it leads
> people to think that PG *will* cache function results, as opposed to
> *could* cache function results.

I must admit, that was my impression. Are there simple rules for if/when PG
will cache function results?

--  Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote:
>> Unfortunately that's not true at all, or at least not helpful for this
>> problem.  The cachable attribute was poorly named, because it leads
>> people to think that PG *will* cache function results, as opposed to
>> *could* cache function results.

> I must admit, that was my impression. Are there simple rules for if/when PG 
> will cache function results?

It won't; there is no function cache.  What there is is a pass of
constant-folding before a query is run.  For example, if you write
select * from foo where x > sqrt(4);

then the function call "sqrt(4)" will be folded down to a constant "2"
before planning and execution starts, rather than evaluating it again
at each row of foo.  (This also improves the system's ability to use
indexes, etc, so it's a pretty essential thing.)

The point of the poorly-named isCachable attribute is to tell the
constant-folding pass whether it's safe to apply the function in
advance of execution --- ie, does it always return the same output,
given constant inputs?  An example of a non-cachable function is
now().

In 7.3 isCachable has been split into two attributes "immutable"
and "stable", distinguishing functions that are constant for all
time from those whose outputs are constant during any single query.
(sqrt() is immutable, now() is stable, random() is neither.)
These names perhaps will be less likely to mislead people into
thinking that some kind of caching goes on while a query runs.
        regards, tom lane