I am getting a mistake from the deterministic PG function that appears dependent according to after i refer to it as inside a query.
The function is:
CREATE OR REPLACE FUNCTION ircm(starting_money numeric, value numeric, days numeric) RETURNS numeric AS $$ BEGIN -- Calculate effective interest rate, compounded monthly. RETURN 12*(pow((value/starting_money),(1./(12.*(days/365.)))) - 1); END; $$ LANGUAGE plpgsql;
Basically refer to it as inside a simple Choose statement, it really works fine:
SELECT ircm(100.00,60.427500643787215,30) Result: -4.79925436505569765596
However, after i run that identical call from the different Choose statement utilizing a subquery:
SELECT ircm(100.00,m.v::numeric,30) AS result FROM( SELECT 60.427500643787215 AS v ) m
I recieve the mistake:
ERROR: a negative number raised to a non-integer power yields a complex result CONTEXT: PL/pgSQL function "ircm" line 6 at RETURN
Because the calls are realistically equivalent, just how can PG be coming back a mistake for just one call, although not another? I have ensured there's just one meaning of the function "ircm" during my database. I have attempted shedding/adding it again to make certain PG is not in some way caching a corrupted definition.
Which version as well as on which platform is the database running?
Perhaps you have attempted spinning the function in SQL:
CREATE OR REPLACE FUNCTION ircm(numeric, numeric, numeric) RETURNS numeric AS $$ select 12 * (pow(($2 / $1), (1. / (12.* ($3 / 365.)))) - 1); $$ LANGUAGE sql immutable;
I am running v8.4.4 on Solaris, Linux, and Mac OS X and get exactly the same is a result of your plpgsql (and my SQL) version known as directly, and from inside the subquery.