pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStored procedure returning row or resultset
6+ messages / 4 participants
[nested] [flat]
* Stored procedure returning row or resultset
@ 2002-10-14 14:22 Heiko Stoermer <heiko.stoermer@login-solutions.de>
2002-10-14 15:05 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
2002-10-14 15:15 ` Re: Stored procedure returning row or resultset Brian Blaha <bblaha@umr.edu>
0 siblings, 2 replies; 6+ messages in thread
From: Heiko Stoermer @ 2002-10-14 14:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Stored procedure returning row or resultset
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
@ 2002-10-14 15:05 ` Richard Huxton <dev@archonet.com>
2002-10-14 17:17 ` Re: Stored procedure returning row or resultset Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2002-10-14 15:05 UTC (permalink / raw)
To: Heiko Stoermer <heiko.stoermer@login-solutions.de>; 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Stored procedure returning row or resultset
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
2002-10-14 15:05 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
@ 2002-10-14 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-15 08:17 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-10-14 17:17 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Heiko Stoermer <heiko.stoermer@login-solutions.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Stored procedure returning row or resultset
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
2002-10-14 15:05 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
2002-10-14 17:17 ` Re: Stored procedure returning row or resultset Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-10-15 08:17 ` Richard Huxton <dev@archonet.com>
2002-10-15 13:57 ` Re: Stored procedure returning row or resultset Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2002-10-15 08:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Stored procedure returning row or resultset
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
2002-10-14 15:05 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
2002-10-14 17:17 ` Re: Stored procedure returning row or resultset Tom Lane <tgl@sss.pgh.pa.us>
2002-10-15 08:17 ` Re: Stored procedure returning row or resultset Richard Huxton <dev@archonet.com>
@ 2002-10-15 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-10-15 13:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Stored procedure returning row or resultset
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
@ 2002-10-14 15:15 ` Brian Blaha <bblaha@umr.edu>
1 sibling, 0 replies; 6+ messages in thread
From: Brian Blaha @ 2002-10-14 15:15 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-10-15 13:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-14 14:22 Stored procedure returning row or resultset Heiko Stoermer <heiko.stoermer@login-solutions.de>
2002-10-14 15:05 ` Richard Huxton <dev@archonet.com>
2002-10-14 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-15 08:17 ` Richard Huxton <dev@archonet.com>
2002-10-15 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-14 15:15 ` Brian Blaha <bblaha@umr.edu>
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