Re: Performance of pl/pgsql functions?

2012/9/14 Wells Oliver <wellsoliver(at)gmail(dot)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(at)gmail(dot)com