Обсуждение: 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