pl/pgsql can give a real boost if you need to aggregate values in a
way that doesn't have an existing SQL function. For instance, there
is an SQL function which will take an average. However, what if you
need some other statistical type of average which isn't supported in
SQL? For the sake of an example, let's say that median isn't
supported in SQL (it may be, I haven't checked). If you need to take
the median of 10000 rows, it will be very slow if you do all that on
the client. It will be much faster to write median in pl/pgsql and
just do it on the srver.
Also, pl/pgsql queries get optimized before they are run, so there is
some benefit there, too.