2012/9/14 Wells Oliver <wellsoliver@gmail.com>:
> Do these tend to perform well? I have some simple formulas in functions like
> so:
>
> CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
> RETURNS numeric AS
> $BODY$
>
> declare ret numeric;
>
> begin
> select (a+b) / c::numeric into ret;
> return round(ret, 3);
> end
>
> $BODY$
> LANGUAGE plpgsql IMMUTABLE COST 100;
it is not good
CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS $$
BEGIN
RETURN round((a + b) / c::numeric), 3)::numeric;
END
$$ LANGUAGE plpgsql IMMUTABLE;
will be significantly faster
probably SQL function will be fastest
CREATE OR REPLACE FUNCTION public.stat_foo(a integer, b integer, c integer)
RETURNS numeric AS $$
SELECT round(($1 + $2) / $3::numeric), 3)::numeric;
$$ LANGUAGE sql;
Regards
Pavel Stehule
>
> The reason I'm doing this is because i repeat this formula in a bunch of
> views and queries, and it's easier to have one function. Would this somehow
> be slower than reproducing the formula in every view its used? I'm hoping
> not...
>
> --
> Wells Oliver
> wellsoliver@gmail.com