在 Azure Database for PostgreSQL 中创建并使用函数

  • 5 分钟

我们已经了解到 PostgreSQL 支持不同的语言。 函数可分为四种不同的类型：

  • 用 SQL 编写的函数。
  • 过程语言函数，用受支持的过程语言（如 PL.pgSQL）编写。
  • 内部函数。
  • C 语言函数。

此外，函数的用途也可以归类为可变、不可变或稳定。

可变（默认）函数可以修改数据库，但在使用相同的输入参数时，不一定每次都返回相同的结果。 因此，每次调用此函数时，都必须进行重新计算。

稳定函数无法修改数据库，但在被传递了相同参数并在同一语句中运行的情况下会返回相同的结果。 如果多次调用此函数，查询优化器可以使用最后一次调用该函数的结果。

不可变函数无法修改数据库，但在被传递了相同参数的情况下会返回相同的结果，不管调用它的查询是什么样的。

函数的波动性对查询优化器处理函数的效率有很大影响。

创建函数

函数返回单个值并且可以在 SELECT 语句中使用。

用于创建函数的语法是：

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

与存储过程一样，$$ 符号用于开始和结束字符串。

函数采用以下参数：

  • name -（可选）包括架构名称。
  • argmode - 参数的模式。 可以是 IN、OUT、INOUT 或 VARIADIC。 默认值为 IN。 VARDIAC 是相同类型的未定义数量的输入参数，后跟 OUT 参数。 OUT 和 INOUT 参数不能与 RETURNS TABLE 表示法一起使用。
  • argname - 参数名称。
  • argtype - 参数数据类型。 可以是基类型、复合类型或域类型，也可以引用表列类型。 列类型将编写为 table_name.column_name%TYPE。 此数据类型有助于使函数独立于表定义更改。
  • t_expr - 如果未指定参数，则为（相同类型的）默认值。 只有 IN 和 INOUT 参数具有默认值。 具有默认值的参数后面的输入参数也必须具有默认值。
  • rettype - 返回数据类型，可以是基类型、复合类型或域类型，也可以引用表列类型。 如果函数未返回值，请将返回类型指定为 void。 如果有 OUT 或 INOUT 参数，则可以省略 RETURNS 子句。 如果存在，它必须同意输出参数隐含的结果类型：如果有多个输出参数，或者与单个输出参数的类型相同，则为 RECORD。 SETOF 修饰符指示函数返回一组项，而不是单个项。 通过编写 table_name 引用列的类型。
  • column_name - RETURNS TABLE 语法中的输出列的名称。 此参数声明一个命名的 OUT 参数，例外情况是 RETURNS TABLE 也隐含了 RETURNS SETOF。
  • column_type - RETURNS TABLE 语法中输出列的数据类型。
  • lang_name - 用于编写过程的语言。 如果指定了 sql_body，则默认值为 sql。 可以是 sql、c、internal 或用户定义过程语言的名称，例如 plpgsql。

使用关键字 IMMUTABLE、STABLE 或 VOLATILE 作为有关函数的查询优化器的提示。 VOLATILE 是默认值。

调用函数

通过将任何相关参数传递给查询，可以在查询中使用函数。 例如：

SELECT myfunction(3), CatID, CatName
    FROM myCats

内置函数

PostgreSQL 包含许多可在查询中使用的内置函数。 这些内容涉及比较、聚合数据、数学函数等。有关 PostgreSQL 函数的完整列表，请参阅联机文档。

内置字符串函数的示例是 substring。

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

此函数需要三个输入参数：

  • String（文本类型）
  • FROM start（整数类型）
  • FOR count（整数类型）

substring 返回输入文本的一部分，从 start 字符开始，并在 count 字符后停止。 例如：

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

此函数与 substr 相同：

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

注意

如果你熟悉函数，你会注意到第一个版本使用关键字而不是逗号来分隔参数。 PostgreSQL 提供这两个版本的函数。