Postgresql 中文操作指南

CREATE FUNCTION

CREATE FUNCTION — 定义新函数

Synopsis

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

Description

CREATE FUNCTION 定义了一个新函数。 CREATE OR REPLACE FUNCTION 将创建一个新函数或替换现有定义。为了能够定义函数，用户必须具有该语言的 USAGE 权限。

如果包括模式名称，则会在指定的模式中创建函数。否则，它将在当前模式中创建。新函数的名称不得与在同一模式中具有相同输入参数类型的任何现有函数或过程匹配。但是，具有不同参数类型的函数和过程可以共享名称（这称为 overloading ）。

要替换现有函数的当前定义，请使用 CREATE OR REPLACE FUNCTION 。通过这种方式无法更改函数的名称或参数类型（如果您尝试，实际上将创建独立的新函数）。此外， CREATE OR REPLACE FUNCTION 不允许您更改现有函数的返回类型。要做到这一点，您必须删除并重新创建该函数。（当使用 OUT 参数时，这意味着您只能通过删除函数来更改 OUT 参数的类型。）

当 CREATE OR REPLACE FUNCTION 用于替换现有函数时，函数的所有权和权限不会改变。函数的其他所有属性都会被分配为命令中指定或隐含的值。您必须拥有该函数才能替换它（包括成为所有角色的成员）。

如果您删除并重新创建了一个函数，那么新函数与旧函数不是同一实体；您将必须删除引用旧函数的现有规则、视图、触发器等等。使用 CREATE OR REPLACE FUNCTION 更改函数定义，而不会中断引用该函数的对象。此外， ALTER FUNCTION 可用于更改现有函数的大部分辅助属性。

创建函数的用户成为该函数的所有者。

要能够创建函数，您必须对参数类型和返回类型具有 USAGE 权限。

请参阅 Section 38.3 了解更多有关编写函数的信息。

Parameters

  • name

    • 要创建的函数的名称（可以选择用模式限定）。

  • argmode

    • 一种参数模式： IN 、 OUT 、 INOUT 或 VARIADIC 。如果省略，则默认为 IN 。只有 OUT 的参数可以紧跟 VARIADIC 。此外， OUT 和 INOUT 参数不能与 RETURNS TABLE 标记法一起使用。

  • argname

    • 参数名称。一些语言（包括 SQL 和 PL/pgSQL）允许您在函数体中使用名称。对于其他语言，输入参数的名称只是额外的文档，因为就函数本身而言; 但您可以在调用函数时使用输入参数名称来提高可读性（参见 Section 4.3 ）。在任何情况下，输出参数的名称都很重要，因为它定义了结果行类型中的列名称。（如果您省略输出参数的名称，系统将选择一个默认列名称。）

  • argtype

    • 函数参数的数据类型（可选的模式限定），如果有的话。参数类型可以是基础类型、复合类型或域名类型，或者可以引用表列的类型。

    • 根据实现语言，还可能允许指定“伪类型”，如 cstring 。伪类型表明实际的参数类型要么不完整指定，要么超出了普通 SQL 数据类型的集合。

    • 通过编写 _table_name . column_name %TYPE_ 来引用列的类型。使用此功能有时有助于使函数独立于表定义的更改。

  • default_expr

    • 如果未指定参数，则用作默认值的表达式。该表达式必须可强制转换为参数的参数类型。只有输入（包括 INOUT ）参数可以具有默认值。在具有默认值的参数后跟的所有输入参数也必须具有默认值。

  • rettype

    • 返回数据类型（可选模式限定）。返回类型可以是基础、复合或域名类型，或者可以引用表列的类型。根据实现语言，还可能允许指定“伪类型”，如 cstring 。如果函数不应返回一个值，请将 void 指定为返回类型。

    • 当有 OUT 或 INOUT 参数时，可以省略 RETURNS 从句。如果存在，它必须与输出参数隐含的结果类型一致： RECORD （如果有多个输出参数）或与单个输出参数类型相同。

    • SETOF 修饰词表明函数将返回一组项目，而不是单个项目。

    • 通过编写 _table_name . column_name %TYPE_ 来引用列的类型。

  • column_name

    • 在 RETURNS TABLE 语法中输出列的名称。这实际上是声明命名 OUT 参数的另一种方式，除了 RETURNS TABLE 还隐含 RETURNS SETOF 。

  • column_type

    • 在 RETURNS TABLE 语法中输出列的数据类型。

  • lang_name

    • 实现函数的语言的名称。它可以是 sql 、 c 、 internal 或用户定义的过程语言的名称，例如 plpgsql 。如果指定了 sql_body ，则默认为 sql 。使用单引号括住名称已弃用，需要匹配大小写。

  • TRANSFORM { FOR TYPE _type_name } [, …​ ] }_

    • 列出应应用于该函数调用的转换。转换在 SQL 类型和特定于语言的数据类型之间转换；参见 CREATE TRANSFORM 。过程语言实现通常具有对内置类型的硬编码知识，因此此处不需要列出这些知识。如果过程语言实现不知道如何处理一个类型并且没有提供转换，它将回到转换数据类型的默认行为，但这取决于实现。

  • WINDOW

    • WINDOW 表明函数是一个 window function 而不是一个普通函数。这目前仅对用 C 编写的函数有用。替换现有函数定义时 WINDOW 属性无法更改。

  • IMMUTABLE_STABLE_VOLATILE

    • 这些属性通知查询优化器有关函数行为的信息。最多可以指定一个选项。如果以上都不显示， VOLATILE 是默认假设。

    • IMMUTABLE 表明函数不能修改数据库，并且在给出相同参数值时始终返回相同的结果；也就是说，它不执行数据库查找或以其他方式使用不在其参数列表中直接存在的信息。如果给出了这个选项，那么用所有常量参数调用的任何函数都可以立即用函数值替换。

    • STABLE 表示函数不能修改数据库，并且在单个表扫描中，对于相同的参数值，它将始终返回相同的结果，但是它的结果可能会在 SQL 语句之间发生变化。这是函数的合适选择，其结果取决于数据库查找、参数变量（例如当前时区）等。（它不适合 AFTER 希望查询由当前命令修改的行。）：还要注意， current_timestamp 系列函数被视为稳定的，因为它们的值在事务中不会改变。

    • VOLATILE 表示函数值甚至可以在一个表扫描中发生改变，因此无法进行优化。相对而言，很少有数据库函数会这样不稳定；有一些例子，比如说 random() 、 currval() 、 timeofday() 。但请注意，任何具有副作用的函数都必须归类为不稳定的，即使其结果相当可预测，以防止调用被优化掉； setval() 就是一个例子。

    • 有关其他详细信息，请参阅 Section 38.7 。

  • LEAKPROOF

    • LEAKPROOF 表示函数没有任何副作用。除了返回值之外，它没有透露任何有关其参数的信息。例如，一个对于某些参数值会抛出错误消息但对于其他参数值不会抛出错误消息的函数，或者在任何错误消息中都包含参数值的函数就不是防泄露的。在针对使用 security_barrier 选项或启用行级安全性的表创建的视图执行查询时，这会影响系统执行查询的方式。系统将在查询本身中包含有非防泄露函数的任何用户提供的条件之前，强制执行源自安全策略和安全障碍视图的条件，以防止数据意外泄露。标记为防泄露的函数和运算符被假定为可信，并且可以在源自安全策略和安全障碍视图的条件之前执行。此外，不需要从安全障碍视图或表中获取参数的函数或者不从其获取任何参数的函数不必标记为防泄露即可在安全条件之前执行。请参阅 CREATE VIEW 和 Section 41.5 。此选项只能由超级用户设置。

  • CALLED ON NULL INPUT_RETURNS NULL ON NULL INPUT_STRICT

    • CALLED ON NULL INPUT （默认设置）表示当某些参数为空时，将正常调用函数。然后，函数作者就有责任在必要时检查空值并做出适当的响应。

    • RETURNS NULL ON NULL INPUT 或 STRICT 表示当任何参数为空时，函数总是返回空。如果指定此参数，那么在存在空参数时不会执行函数；而是自动假定一个空结果。

  • [EXTERNAL] SECURITY INVOKER_ [EXTERNAL] SECURITY DEFINER_

    • SECURITY INVOKER 表示将使用调用它的用户的权限来执行函数。这是默认设置。 SECURITY DEFINER 指定将使用拥有该函数的用户的权限来执行函数。有关如何安全地编写 SECURITY DEFINER 函数的信息， see below 。

    • 为了符合 SQL 规范，允许使用关键词 EXTERNAL ，但它是可选的，因为与 SQL 不同，此功能适用于所有函数，不只是外部函数。

  • PARALLEL

    • PARALLEL UNSAFE 表示无法在并行模式下执行函数，而这种函数的存在会迫使 SQL 语句生成一个串行执行计划。这是默认设置。 PARALLEL RESTRICTED 表示可以在并行模式下执行函数，但执行仅限于并行组领导者。 PARALLEL SAFE 表示可以在并行模式下安全地运行函数，不受限制。

    • 如果函数修改了任何数据库状态，或者它们对事务进行了更改（例如使用子事务），或者它们访问顺序或试图对设置做出持久性更改（例如， setval ），则应将函数标记为并行不安全。如果函数访问临时表、客户端连接状态、游标、预处理语句或在并行模式下系统无法同步的其他后端本地状态（例如， setseed 只能由组领导者执行，因为其他进程所做的更改不会反映在领导者中），则应将其标记为并行受限。通常来说，如果将函数标记为受限或不安全时的状态是安全的，或者在实际上不安全时的状态是受限的，那么在并行查询中使用时它可能会抛出错误或产生错误答案。C 语言函数在错误标记的情况下理论上可能会表现出完全未定义的行为，因为系统无法保护自身免受任意 C 代码的影响，但在大多数情况下，结果不会比任何其他函数差。如果有疑问，应将函数标记为 UNSAFE ，这是默认设置。

  • COST execution_cost

    • 一个正数，表示以 cpu_operator_cost 为单位的函数估计执行成本。如果函数返回一个集合，则这是每行返回的成本。如果没有指定成本，C 语言函数和内部函数假设为 1 个单位，而其他所有语言的函数假设为 100 个单位。较高值会导致规划器尝试避免对函数进行比必要次数更多的求值。

  • ROWS result_rows

    • 一个正数，表示计划器应期望函数返回的行数估计值。仅当声明函数返回一个集合时才允许这样做。默认假设为 1000 行。

  • SUPPORT support_function

    • planner support function 的名称（选择性模式限定），用于此函数。有关详细信息，请参阅 Section 38.11 。你必须是超级用户才能使用此选项。

  • configuration_parameter__value

    • 当进入函数时， SET 子句会导致将指定的配置参数设置为指定值，然后在退出函数时将其还原为其先前的值。 SET FROM CURRENT 将在执行 CREATE FUNCTION 时当前的参数值保存为在进入函数时应用的值。

    • 如果将 SET 子句附加到一个函数，那么在函数内部为同一变量执行 SET LOCAL 命令的影响就会限制在该函数内：配置参数的先前值仍然会在函数退出时还原。但是，普通的 SET 命令（没有 LOCAL ）会覆盖 SET 子句，就像对之前的 SET LOCAL 命令所做的一样：此类命令的影响会在函数退出后持续存在，除非当前事务回滚。

    • 请参阅 SET 和 Chapter 20 以获取有关允许的参数名称和值的详细信息。

  • definition

    • 定义函数的字符串常量；含义取决于语言。它可以是内部函数名称、对象文件路径、SQL 命令或过程语言文本。

    • 在编写函数定义字符串时，通常使用美元引用（请参阅 Section 4.1.2.4 ）会比正常的单引号语法更有帮助。在不使用美元引用时，函数定义中的任何单引号或反斜杠都必须通过双写进行转义。

  • obj_file_, _link_symbol

    • AS 子句的此形式用于动态可加载的 C 语言函数，此时，C 语言源代码中的函数名称与 SQL 函数的名称不同。字符串 obj_file 是包含已编译 C 函数的共享库文件的文件名，并对其进行解释，具体方式与 LOAD 命令类似。字符串 link_symbol 是函数的链接符号，即 C 语言源代码中函数的名称。如果省略了链接符号，则假定它与正在定义的 SQL 函数的名称相同。所有函数的 C 名称必须不同，因此你必须为重载的 C 函数提供不同的 C 名称（例如，使用参数类型作为 C 名称的一部分）。

    • 当重复的 CREATE FUNCTION 调用引用同一对象文件时，每个会话只加载一次文件。要卸载并重新加载文件（也许是在开发期间），请开始一个新的会话。

  • sql_body

    • LANGUAGE SQL 函数的主体。这可以是一个单一语句

RETURN expression

  • or a block

BEGIN ATOMIC
  statement;
  statement;
  ...
  statement;
END

  • 这类似于将函数主体的文本作为字符串常量来编写（请参见上面的 definition ），但有一些区别：此形式仅适用于 LANGUAGE SQL ，字符串常量形式适用于所有语言。此形式在函数定义时会进行解析，字符串常量形式在执行时进行解析；因此，此形式不能支持多态参数类型和其他在函数定义时无法解析的结构。此形式跟踪函数与函数主体中使用的对象之间的依赖关系，因此 DROP …​ CASCADE 将正常工作，而使用字符串文本的形式可能会导致悬空函数。最后，此形式与 SQL 标准和其他 SQL 实现更兼容。

Overloading

PostgreSQL 允许函数 overloading ；也就是说，同一个名称可以用于几个不同的函数，只要它们的输入参数类型不同即可。无论您是否使用它，这种能力在数据库中调用函数时都会带来安全预防措施，其中一些用户不信任其他用户；请参见 Section 10.3 。

如果两个函数具有相同名称和 input 参数类型，则认为这两个函数是相同的，忽略任何 OUT 参数。因此，例如，这些声明相互冲突：

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

具有不同参数类型列表的函数在创建时将不被视为冲突，但如果提供了默认值，它们可能会在使用中冲突。例如，考虑

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

调用 foo(10) 会失败，因为无法确定应该调用哪个函数。

Notes

完整的 SQL 类型语法允许声明函数的参数和返回值。但是，带括号的类型修饰符（例如，类型 numeric 的精度字段）会被 CREATE FUNCTION 丢弃。因此，例如， CREATE FUNCTION foo (varchar(10)) …​ 与 CREATE FUNCTION foo (varchar) …​ 完全相同。

在 CREATE OR REPLACE FUNCTION 中用现有的函数替换现有函数时，对更改参数名称有一些限制。您无法更改已分配给任何输入参数的名称（尽管您可以向以前没有名称的参数添加名称）。如果有多个输出参数，则不能更改输出参数的名称，因为这会更改描述函数结果的匿名复合类型的列名称。这些限制是为了确保在替换函数时，对函数的现有调用不会停止工作。

如果用 VARIADIC 参数声明函数 STRICT ，则严格性检查会测试变长数组 as a whole 是否为非空。如果该数组具有空元素，则仍将调用该函数。

Examples

使用 SQL 函数添加两个整数：

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

使用参数名称和未加引号的主体编写的以更符合 SQL 的样式编写的相同函数：

CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;

在 PL/pgSQL 中使用参数名称递增整数：

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

返回一个包含多个输出参数的记录：

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

您可以使用显式命名的复合类型更详细地执行相同的操作：

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

返回多列的另一种方法是使用 TABLE 函数：

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

但是， TABLE 函数与前一个示例不同，因为它实际上返回记录 set ，而不仅仅是一个记录。

Writing SECURITY DEFINER Functions Safely

因为 SECURITY DEFINER 函数是使用其所有者的权限执行的，所以需要小心确保无法滥用该函数。为了安全起见，应将 search_path 设置为排除任何不受信任用户可写的模式。这可以防止恶意用户创建掩盖函数打算使用的对象（例如，表、函数和运算符）的对象。在这方面特别重要的是临时表模式，它默认情况下首先被搜索，并且通常是任何人都可以写入的。可以通过强制临时模式最后被搜索来获得安全的安排。为此，请将 pg_temp 写为 search_path 中的最后一个条目。此函数说明了安全用法：

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

此函数的目的是访问表 admin.pwds 。但如果没有 SET 子句，或 SET 子句只提到了 admin ，可以通过创建一个名为 pwds 的临时表来破坏此函数。

如果安全定义者函数打算创建角色，并且以非超级用户身份运行，则还应使用 SET 子句将 createrole_self_grant 设置为已知值。

需要牢记的另一点是，默认情况下，会向 PUBLIC 授予新创建函数的执行权限（有关更多信息，请参阅 Section 5.7 ）。您经常需要将安全定义器函数的使用限制为部分用户。为此，您必须撤消默认 PUBLIC 权限，然后选择性地授予执行权限。为了避免在所有用户均可访问新函数的时间段内创建它，并在单一事务中设置该权限。例如：

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

Compatibility

SQL 标准中定义了一个 CREATE FUNCTION 命令。PostgreSQL 实现可以兼容使用，但有许多扩展。相反，SQL 标准指定了一些 PostgreSQL 中未实现的可选特性。

以下是一些重要的兼容性问题：

可以编写简单的 LANGUAGE SQL 函数使其既符合标准，又可移植到其他实现。使用高级特性、优化属性或其他语言的更复杂函数，在很大程度上必然是特定于 PostgreSQL 的。

See Also

ALTER FUNCTION 、 DROP FUNCTION 、 GRANT 、 LOAD 、 REVOKE