> When to use stored procedure / UDF?

还有functions,做得差不多但不完全一样,那些从一开始就在那里.

使用LANGUAGE sql的函数基本上只是在函数包装器中使用普通sql命令的批处理文件(因此原子,总是在单个事务中运行)接受参数. sql函数中的所有语句都是一次计划的,这与在另一个语句和may affect the order in which locks are taken.之后执行一个语句略有不同

除此之外,最成熟的语言是PL/pgSQL(LANGUAGE plpgsql).它运行良好,并且在过去十年中每次发布都得到了改进,但它最适合作为sql命令的粘合剂.它不适用于繁重的计算(除了使用sql命令).

PL / pgsql函数执行像prepared statements这样的查询.重新使用缓存的查询计划会减少一些计划开销,并使它们比等效的sql语句快一点,这可能会因环境而显着影响.它可能也有副作用,就像在这个相关的问题：

> PL/pgSQL issues when function used twice (caching problem ?)

这具有预处理语句的优点和缺点 – as discussed in manual.对于具有不规则数据分布和变化参数的表的查询,当针对给定参数的优化执行计划的增益超过re的成本时,具有EXECUTE的动态sql可能表现更好. -规划.

由于Postgres 9.2通用执行计划仍在为会话缓存,但是,quoting the manual：

This occurs immediately for prepared statements with no parameters;
otherwise it occurs only after five or more executions produce plans
whose estimated cost average (including planning overhead) is more
expensive than the generic plan cost estimate.

如果没有(ab)使用EXECUTE,我们大部分时间都会获得最佳效果(减去一些额外的开销).详情请见What’s new in PostgreSQL 9.2 of the PostgreSQL Wiki.

您可以通过服务器端功能赢得大奖,以防止从您的应用程序额外往返数据库服务器.让服务器一次尽可能多地执行,只返回定义良好的结果.

避免嵌套复杂函数,尤其是表函数(RETURNING SETOF记录或TABLE(…)).函数是黑盒子,构成查询规划器的优化障碍.它们是单独优化的,不与外部查询结合使用,这使得计划更简单,但可能导致计划不完美.而且,无法有效地预测功能的成本和结果大小.

此规则的例外是简单的sql函数(LANGUAGE sql),它可以是“inlined” – if some preconditions are met.阅读有关查询计划程序如何在此presentation by Neil Conway中工作的更多信息(高级内容).

在Postgresql中,函数始终在单个事务中自动运行.所有这些都成功或没有成功.如果发生异常,则回滚所有内容.但是有error handling ……

这也是为什么函数不完全是“存储过程”的原因(尽管有时使用该术语,误导性).某些命令(如VACUUM,CREATE INDEX CONCURRENTLY或CREATE DATABASE)无法在事务块内运行,因此不允许在函数中使用它们. (在sql程序中,从第11页开始.可能会在以后添加.)

多年来我编写了数千个plpgsql函数.