> What are the differences between “Stored Procedures” and “Stored Functions”?

Postgres 11终于推出了真正的stored procedures：

> When to use stored procedure / user-defined function?

Functions在Postgres中是原子的,并且在自己的事务中自动运行,除非在外部事务中调用.它们总是在单个事务中运行,并且完全成功或失败.因此,无法在函数内开始或提交事务.并且不允许在事务块中运行的VACUUM或CREATE INDEX CONCURRENTLY等命令.

Per documentation on PL/pgSQL:

Functions and trigger procedures are always executed within a
transaction established by an outer query — they cannot start or
commit that transaction,since there would be no context for them to
execute in. However,a block containing an EXCEPTION clause
effectively forms a subtransaction that can be rolled back without
affecting the outer transaction.

Error handling:

By default,any error occurring in a PL/pgsql function aborts
execution of the function,and indeed of the surrounding transaction
as well. You can trap errors and recover from them by using a BEGIN
block with an EXCEPTION clause.

有特殊例外,包括但不限于：

>写入日志文件的数据
> changes made to a sequence

Important: Some Postgresql data types and functions have special rules
regarding transactional behavior. In particular,changes made to a
sequence (and therefore the counter of a column declared using serial)
are immediately visible to all other transactions and are not rolled
back if the transaction that made the changes aborts.

>准备好的陈述

> SQL Fiddle演示

> dblink调用(或类似)

> Does Postgres support nested or autonomous transactions?