Postgresql 中文操作指南

46.9. Utility Functions #

plpy 模块还提供以下功能：

The plpy module also provides the functions

plpy.error 和 plpy.fatal 实际上会引发一个 Python 异常，如果未捕获该异常，它会传播至调用查询，导致当前事务或子事务中止。 raise plpy.Error(_msg )_ 和 raise plpy.Fatal(_msg )_ 等同于调用 plpy.error(_msg )_ 和 plpy.fatal(_msg ) , respectively but the _raise 形式不允许传递关键字参数。其他函数仅会生成不同优先级的消息。是否向客户端报告特定优先级的消息、写入服务器日志中，还是两者都做，都由 log_min_messages 和 client_min_messages 配置变量控制。有关更多信息，请参阅 Chapter 20 。

plpy.error and plpy.fatal actually raise a Python exception which, if uncaught, propagates out to the calling query, causing the current transaction or subtransaction to be aborted. raise plpy.Error(_msg)_ and raise plpy.Fatal(_msg)_ are equivalent to calling plpy.error(_msg)_ and plpy.fatal(_msg), respectively but the _raise form does not allow passing keyword arguments. The other functions only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.

msg 参数作为位置参数提供。为实现向后兼容性，可以提供多于一个位置参数。在这种情况下，位置参数元组的字符串表示形式将成为向客户端报告的消息。

The msg argument is given as a positional argument. For backward compatibility, more than one positional argument can be given. In that case, the string representation of the tuple of positional arguments becomes the message reported to the client.

接受以下关键字专用参数：

The following keyword-only arguments are accepted:

将作为关键字专用参数传入的对象的字符串表示形式用于丰富向客户端报告的消息。例如：

The string representation of the objects passed as keyword-only arguments is used to enrich the messages reported to the client. For example:

CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
plpy.error("custom exception message",
           detail="some info about exception",
           hint="hint for users")
$$ LANGUAGE plpython3u;

=# SELECT raise_custom_exception();
ERROR:  plpy.Error: custom exception message
DETAIL:  some info about exception
HINT:  hint for users
CONTEXT:  Traceback (most recent call last):
  PL/Python function "raise_custom_exception", line 4, in <module>
    hint="hint for users")
PL/Python function "raise_custom_exception"

另一组实用程序函数是_plpy.quote_literal(string), _plpy.quote_nullable(_string), and _plpy.quote_ident(_string)_. 它们等效于 Section 9.4中描述的内置引用函数。它们在构建临时查询时非常有用。来自 Example 43.1的动态 SQL 的 PL/Python 等效项将是：

Another set of utility functions are plpy.quote_literal(_string), _plpy.quote_nullable(_string), and _plpy.quote_ident(_string)_. They are equivalent to the built-in quoting functions described in Section 9.4. They are useful when constructing ad-hoc queries. A PL/Python equivalent of dynamic SQL from Example 43.1 would be:

plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
    plpy.quote_ident(colname),
    plpy.quote_nullable(newvalue),
    plpy.quote_literal(keyvalue)))