pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql function executed multiple times for each return value
3+ messages / 3 participants
[nested] [flat]
* plpgsql function executed multiple times for each return value
@ 2011-10-06 17:59 Steve Northamer <stevenorthamer@gmail.com>
2011-10-08 15:50 ` Re: plpgsql function executed multiple times for each return value David Johnston <polobo@yahoo.com>
2011-10-08 16:32 ` Re: plpgsql function executed multiple times for each return value Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Steve Northamer @ 2011-10-06 17:59 UTC (permalink / raw)
To: pgsql-sql
We have a plpgsql function called paymentcalc, which calculates the
payment necessary to pay off a loan. It's defined like this:
CREATE OR REPLACE FUNCTION paymentcalc(IN amount numeric, IN
interestrate numeric, IN termmonths integer, IN paymentfreq integer, IN
dueday1 integer, IN dueday2 integer, IN borrowdate date, IN firstdue
date, IN gapins character, IN lifeins character, IN disabilityins
character, OUT payment numeric, OUT finalpayment numeric, OUT finaldue date)
RETURNS record AS
...
LANGUAGE 'plpgsql' STABLE;
We want to execute this function, with inputs from a table, and return
the calculated values as separate columns:
select (p).payment, (p).finalpayment, (p).finaldue
from
(select paymentcalc(amount, interestrate / 100, termmonths,
paymentfreq, dueday1, dueday2,
borrowdate, firstdue, gapins, lifeins, disins) as p
from apps
where id = 100) s
This works, but using "RAISE NOTICE" we've determined that this executes
the paymentcalc function 3 times! It seems to execute the function once
for each return value. The function is time consuming, and we want to
execute it only once.
Thinking that maybe a table returning function would work better, we
tried this:
CREATE OR REPLACE FUNCTION paymentcalc2(IN amount numeric, IN
interestrate numeric, IN termmonths integer, IN paymentfreq integer, IN
dueday1 integer, IN dueday2 integer, IN borrowdate date, IN firstdue
date, IN gapins character, IN lifeins character, IN disabilityins character)
RETURNS SETOF paymentcalc_return AS
...
LANGUAGE 'plpgsql' STABLE;
However, we now have difficulty trying to send parameters from a table
as inputs to this function. We want to do something like:
select *
from paymentcalc2(amount, interestrate / 100, termmonths, paymentfreq,
dueday1, dueday2,
borrowdate, firstdue, gapins, lifeins, disins)
from apps
where id = 100
But obviously this query won't work with two "from"s. We can't do a
join between paymentcalc2 and apps, because we get "invalid reference to
FROM-clause entry for table apps".
So my questions are: 1) How do we cause the paymentcalc function to be
executed only once? and 2) How do we call a table returning function
with inputs from a table?
Thank you very much!
Steve
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql function executed multiple times for each return value
2011-10-06 17:59 plpgsql function executed multiple times for each return value Steve Northamer <stevenorthamer@gmail.com>
@ 2011-10-08 15:50 ` David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2011-10-08 15:50 UTC (permalink / raw)
To: Steve Northamer <stevenorthamer@gmail.com>; +Cc: pgsql-sql
> So my questions are: 1) How do we cause the paymentcalc function to be executed only once? and 2) How do we call a table returning function with inputs from a table?
>
> Thank you very much!
>
> Steve
>
WITH func AS (
SELECT FUNC(...) AS func_result FROM ...
)
SELECT (func.func_result).* FROM func
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql function executed multiple times for each return value
2011-10-06 17:59 plpgsql function executed multiple times for each return value Steve Northamer <stevenorthamer@gmail.com>
@ 2011-10-08 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2011-10-08 16:32 UTC (permalink / raw)
To: Steve Northamer <stevenorthamer@gmail.com>; +Cc: pgsql-sql
Steve Northamer <stevenorthamer@gmail.com> writes:
> So my questions are: 1) How do we cause the paymentcalc function to be
> executed only once?
In recent versions, I think marking it volatile would be sufficient.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-10-08 16:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-06 17:59 plpgsql function executed multiple times for each return value Steve Northamer <stevenorthamer@gmail.com>
2011-10-08 15:50 ` David Johnston <polobo@yahoo.com>
2011-10-08 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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