Postgresql 中文操作指南

43.1. Overview #

PL/pgSQL 是 PostgreSQL 数据库系统的一个可加载过程语言。PL/pgSQL 的设计目标是创建一个可加载过程语言,该语言

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that

用 PL/pgSQL 创建的函数可以在可以使用内置函数的任何地方使用。例如,可以创建复杂的条件计算函数,然后使用它们来定义运算符或在索引表达式中使用它们。

Functions created with PL/pgSQL can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.

在 PostgreSQL 9.0 及更高版本中,PL/pgSQL 默认安装。然而它仍然是一个可加载模块,因此尤其注重安全性的管理员可以选择将其移除。

In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.

43.1.1. Advantages of Using PL/pgSQL #

SQL 是 PostgreSQL 和大多数其他关系数据库用作查询语言的语言。它具有可移植性和易学性。但每一句 SQL 语句都必须由数据库服务器单独执行。

SQL is the language PostgreSQL and most other relational databases use as query language. It’s portable and easy to learn. But every SQL statement must be executed individually by the database server.

这意味着您的客户端应用程序必须将每个查询发送到数据库服务器,等待它被处理,接收并处理结果,进行一些计算,然后向服务器发送进一步的查询。所有这些都会产生进程间通信,如果您的客户端在与数据库服务器不同的机器上,它还将产生网络开销。

That means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.

使用 PL/pgSQL,您可以将一个计算块和一系列查询 inside 数据库服务器分组,从而既拥有过程语言的强大功能,又拥有 SQL 的易用性,但大大节省了客户端/服务器通信开销。

With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.

与不使用存储函数的应用程序相比,这可能会显著提高性能。

This can result in a considerable performance increase as compared to an application that does not use stored functions.

此外,使用 PL/pgSQL,您可以使用 SQL 的所有数据类型、运算符和函数。

Also, with PL/pgSQL you can use all the data types, operators and functions of SQL.

43.1.2. Supported Argument and Result Data Types #

用 PL/pgSQL 编写的函数可以接受服务器支持的任何标量或数组数据类型作为参数,并且可以返回这些类型之一的结果。它们还可以接受或返回按名称指定的任何复合类型(行类型)。也可以将 PL/pgSQL 函数声明为接受 record,这意味着任何复合类型都可以作为输入,或声明为返回 record,这意味着结果是一个行类型,其列由 Section 7.2.1.4 中讨论的调用查询中的规范确定。

Functions written in PL/pgSQL can accept as arguments any scalar or array data type supported by the server, and they can return a result of any of these types. They can also accept or return any composite type (row type) specified by name. It is also possible to declare a PL/pgSQL function as accepting record, which means that any composite type will do as input, or as returning record, which means that the result is a row type whose columns are determined by specification in the calling query, as discussed in Section 7.2.1.4.

可以通过使用 VARIADIC 标记来声明 PL/pgSQL 函数以接受可变数量的参数。这与 SQL 函数的工作方式完全相同,如 Section 38.5.6 中所述。

PL/pgSQL functions can be declared to accept a variable number of arguments by using the VARIADIC marker. This works exactly the same way as for SQL functions, as discussed in Section 38.5.6.

还可以声明 PL/pgSQL 函数以接受和返回 Section 38.2.5 中描述的多态类型,从而允许函数处理的实际数据类型在每次调用时发生变化。示例出现在 Section 43.3.1 中。

PL/pgSQL functions can also be declared to accept and return the polymorphic types described in Section 38.2.5, thus allowing the actual data types handled by the function to vary from call to call. Examples appear in Section 43.3.1.

PL/pgSQL 函数也可以声明用于返回任意数据类型的“集”(或表),该数据类型可以作为单个实例返回。此类函数通过针对结果集的每个所需元素执行 RETURN NEXT 或使用 RETURN QUERY 来输出查询评估结果从而生成其输出。

PL/pgSQL functions can also be declared to return a “set” (or table) of any data type that can be returned as a single instance. Such a function generates its output by executing RETURN NEXT for each desired element of the result set, or by using RETURN QUERY to output the result of evaluating a query.

最后,如果 PL/pgSQL 函数没有任何有用的返回值,则可以声明该函数返回 void 。(也可以在这种情况下将其写为过程。)

Finally, a PL/pgSQL function can be declared to return void if it has no useful return value. (Alternatively, it could be written as a procedure in that case.)

还可以声明具有输出参数的 PL/pgSQL 函数,以取代返回类型的明确规范。这不会为该语言添加任何基本功能,但通常很方便,特别是用于返回多个值。也可以在 RETURNS TABLE 中使用 RETURNS SETOF 标记法。

PL/pgSQL functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The RETURNS TABLE notation can also be used in place of RETURNS SETOF.

具体示例出现在 Section 43.3.1Section 43.6.1 中。

Specific examples appear in Section 43.3.1 and Section 43.6.1.