CREATE FUNCTION

CREATE FUNCTION — 定义新函数

概要

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
  } ...

说明

CREATE FUNCTION定义新函数。CREATE OR REPLACE FUNCTION将创建新函数或替换现有定义。要能够定义函数，用户必须对该语言拥有USAGE权限。

如果包含架构名称，则函数在指定的架构中创建。否则，在当前架构中创建。新函数的名称不能与同一架构中具有相同输入参数类型的任何现有函数或过程匹配。但是，不同参数类型的函数和过程可以共享一个名称（这称为重载）。

要替换现有函数的当前定义，请使用CREATE OR REPLACE FUNCTION。不能通过这种方式更改函数的名称或参数类型（如果您尝试这样做，实际上将创建一个新的、不同的函数）。此外，CREATE OR REPLACE FUNCTION不会允许您更改现有函数的返回类型。要执行此操作，您必须删除并重新创建函数。（使用OUT参数时，这意味着您不能更改任何OUT参数的类型，除非删除该函数。）

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

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

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

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

有关编写函数的更多信息，请参阅第 38.3 节。

参数

名称

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

argmode

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

argname

参数的名称。一些语言（包括 SQL 和 PL/pgSQL）允许你在函数体中使用名称。对于其他语言，输入参数的名称只是额外的文档，就函数本身而言；但是，你可以在调用函数时使用输入参数名称以提高可读性（请参阅第 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 表示函数是 窗口函数 而不是普通函数。目前，这仅对用 C 编写的函数有用。替换现有函数定义时，不能更改 WINDOW 属性。

IMMUTABLE STABLE VOLATILE

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

IMMUTABLE 表示该函数不能修改数据库，并且在给定相同参数值时始终返回相同的结果；也就是说，它不执行数据库查找或以其他方式使用其参数列表中不存在的信息。如果给出了此选项，则可以使用函数值立即替换对所有常量参数的函数调用。

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

VOLATILE 表示函数值甚至在单个表扫描中也会发生变化，因此无法进行任何优化。从这个意义上说，相对较少的数据库函数是不稳定的；一些示例包括 random()、currval()、timeofday()。但请注意，任何具有副作用的函数都必须归类为不稳定，即使其结果很容易预测，以防止调用被优化掉；一个示例是 setval()。

有关其他详细信息，请参见第 38.7 节。

LEAKPROOF

LEAKPROOF 表示该函数没有副作用。它不会通过其返回值以外的方式透露有关其参数的任何信息。例如，对于某些参数值抛出错误消息或在任何错误消息中包含参数值的函数不是防泄漏的。这会影响系统如何针对使用 security_barrier 选项创建的视图或启用了行级安全性的表执行查询。系统将在包含非防泄漏函数的查询本身中的任何用户提供的条件之前，强制执行安全策略和安全屏障视图中的条件，以防止数据意外泄露。标记为防泄漏的函数和运算符被假定为可信，并且可以在安全策略和安全屏障视图的条件之前执行。此外，不接受参数或未从安全屏障视图或表传递任何参数的函数不必标记为防泄漏，以便在安全条件之前执行。请参见 CREATE VIEW 和第 41.5 节。此选项只能由超级用户设置。

CALLED ON NULL INPUT RETURNS NULL ON NULL INPUT STRICT

在 NULL 输入时调用（默认值）表示当某些参数为 null 时，函数将正常调用。然后，函数作者有责任在必要时检查 null 值并做出适当的响应。

在 NULL 输入时返回 NULL 或 严格 表示当任何参数为 null 时，函数始终返回 null。如果指定此参数，则在存在 null 参数时不会执行函数；而是自动假定为 null 结果。

[外部] 安全调用者 [外部] 安全定义者

安全调用者 表示该函数将使用调用它的用户的权限执行。这是默认值。 安全定义者 指定该函数将使用拥有它的用户的权限执行。有关如何安全编写 安全定义者 函数的信息，请参阅以下内容。

关键字 外部 允许用于 SQL 符合性，但它是可选的，因为与 SQL 不同，此功能适用于所有函数，而不仅仅是外部函数。

并行

并行不安全 表示该函数不能在并行模式下执行，并且在 SQL 语句中存在此类函数会强制执行串行执行计划。这是默认值。 并行受限 表示该函数可以在并行模式下执行，但执行仅限于并行组领导者。 并行安全 表示该函数可以在并行模式下安全运行，不受限制。

如果函数修改任何数据库状态，或者如果它们对事务进行更改（例如使用子事务），或者如果它们访问序列或尝试对设置进行持久更改（例如，setval），则应将函数标记为并行不安全。如果它们访问临时表、客户端连接状态、游标、已准备好的语句或系统无法在并行模式下同步的其他后端本地状态（例如，setseed 不能由组领导者以外的人执行，因为另一个进程所做的更改不会反映在领导者中），则应将它们标记为并行受限。一般来说，如果一个函数在受限或不安全时被标记为安全，或者如果它在实际上不安全时被标记为受限，那么在并行查询中使用它时可能会引发错误或产生错误的答案。从理论上讲，如果 C 语言函数被错误标记，则可能会表现出完全未定义的行为，因为系统无法保护自己免受任意 C 代码的侵害，但在大多数情况下，结果不会比任何其他函数更糟。如有疑问，函数应标记为 不安全，这是默认值。

COST 执行成本

一个正数，以 cpu_operator_cost 为单位，提供函数的估计执行成本。如果函数返回一个集合，则这是每返回一行所需的成本。如果未指定成本，则 C 语言和内部函数假定为 1 个单位，而所有其他语言中的函数假定为 100 个单位。较大的值会导致规划器尽可能避免评估函数。

ROWS 结果行

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

SUPPORT 支持函数

用于此函数的 规划器支持函数 的名称（可选模式限定）。有关详细信息，请参见第 38.11 节。您必须是超级用户才能使用此选项。

配置参数 值

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

如果 SET 子句附加到函数，则在函数内为同一变量执行的 SET LOCAL 命令的效果将限制在函数中：配置参数的先前值仍将在函数退出时还原。但是，普通 SET 命令（无 LOCAL）将覆盖 SET 子句，就像它对先前的 SET LOCAL 命令所做的那样：此类命令的效果将在函数退出后仍然存在，除非回滚当前事务。

有关允许的参数名称和值的更多信息，请参见 SET 和第 20 章。

定义

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

通常，使用美元引用（请参见第 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

或块

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

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

重载

PostgreSQL允许函数重载；也就是说，只要输入参数类型不同，就可以将同一名称用于多个不同的函数。无论是否使用它，此功能都涉及在某些用户不信任其他用户的数据库中调用函数时的安全预防措施；参见第 10.3 节。

如果函数具有相同的名称和输入参数类型，则认为它们是相同的，忽略所有OUT参数。因此，例如，以下声明冲突

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

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

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

调用foo(10)将失败，因为对于应该调用哪个函数存在歧义。

注释

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

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

如果函数使用VARIADIC参数声明为STRICT，则严格性检查将测试可变数组作为一个整体是否非空。如果数组具有空元素，仍将调用函数。

示例

使用 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函数与前面的示例不同，因为它实际上返回一组记录，而不仅仅是一条记录。

安全地编写`SECURITY DEFINER`函数

由于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子句，或仅提及admin的SET子句，则可以通过创建一个名为pwds的临时表来颠覆该函数。

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

需要注意的另一要点是，对于新创建的函数，默认情况下，执行权限授予PUBLIC（有关详细信息，请参见第 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;

兼容性

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

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

OR REPLACE 是 PostgreSQL 扩展。
为了与某些其他数据库系统兼容，argmode 可以写在 argname 之前或之后。但只有第一种方式符合标准。
对于参数默认值，SQL 标准只指定带有 DEFAULT 关键字的语法。带有 = 的语法在 T-SQL 和 Firebird 中使用。
SETOF 修饰符是 PostgreSQL 扩展。
只有 SQL 被标准化为一种语言。
除了 CALLED ON NULL INPUT 和 RETURNS NULL ON NULL INPUT 之外的所有其他属性都未标准化。
对于 LANGUAGE SQL 函数的主体，SQL 标准只指定 sql_body 形式。

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

另请参见

ALTER FUNCTION、DROP FUNCTION、GRANT、LOAD、REVOKE

CREATE FUNCTION ​

概要 ​

说明 ​

参数 ​

重载 ​

注释 ​

示例 ​

安全地编写SECURITY DEFINER函数 ​

兼容性 ​

另请参见 ​

CREATE FUNCTION

概要

说明

参数

重载

注释

示例

安全地编写`SECURITY DEFINER`函数

兼容性

另请参见