plpgsql function executed multiple times for each return value

Поиск
Список
Период
Сортировка
От Steve Northamer
Тема plpgsql function executed multiple times for each return value
Дата
Msg-id 4E8DEC80.3010706@gmail.com
обсуждение исходный текст
Ответы Re: plpgsql function executed multiple times for each return value  (David Johnston <polobo@yahoo.com>)
Re: plpgsql function executed multiple times for each return value  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 pfrom appswhere 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Jasen Betts
Дата:
Сообщение: Re: Edit multiple rows concurrent save
Следующее
От: David Johnston
Дата:
Сообщение: Re: plpgsql function executed multiple times for each return value