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
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Description

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

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

要替换现有功能的当前定义，请使用CREATE OR REPLACE FUNCTION。无法以这种方式更改函数的名称或参数类型(如果尝试过，则实际上是在创建一个新的，不同的函数)。同样，CREATE OR REPLACE FUNCTION不允许您更改现有函数的返回类型。为此，必须删除并重新创建该函数。 (使用OUT参数时，这意味着您只能通过删除函数来更改任何OUT参数的类型.)

使用CREATE OR REPLACE FUNCTION替换现有功能时，该功能的所有权和权限不会更改。所有其他功能属性都被分配了命令中指定或隐含的值。您必须拥有替换该功能的功能(包括成为拥有角色的成员)。

如果删除然后重新创建一个函数，则新功能与旧功能不相同；您将必须删除引用旧功能的现有规则，视图，触发器等。使用CREATE OR REPLACE FUNCTION可以更改函数定义，而不会破坏引用该函数的对象。同样，ALTER FUNCTION可用于更改现有功能的大多数辅助属性。

创建功能的用户将成为该功能的所有者。

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

Parameters

  • name

    • 要创建的函数的名称(可选的模式限定)。

  • argmode

    • 参数的模式：IN，OUT，INOUT或VARIADIC。如果省略，则默认值为IN。只有OUT个参数可以跟随VARIADIC个。同样，OUT和INOUT参数不能与RETURNS TABLE表示法一起使用。

  • argname

    • 参数的名称。某些语言(包括 SQL 和 PL/pgSQL)使您可以在函数主体中使用名称。对于其他语言，就功能本身而言，Importing 参数的名称只是额外的文档。但是您可以在调用函数以提高可读性时使用 Importing 参数名称(请参见Section 4.3)。在任何情况下，输出参数的名称都是有效的，因为它在结果行类型中定义了列名称。 (如果省略输出参数的名称，则系统将选择默认列名称.)

  • argtype

    • 函数参数的数据类型(可选，通过模式限定)(如果有)。参数类型可以是基本，复合或域类型，或者可以引用表列的类型。

根据实现语言的不同，还可能允许指定“伪类型”，例如cstring。伪类型表示实际参数类型未完全指定，或者位于普通 SQL 数据类型集之外。

通过写入table_name.column_name%TYPE来引用列的类型。使用此功能有时可以使函数独立于表定义的更改。

  • default_expr

    • 如果未指定参数，则用作默认值的表达式。该表达式必须对参数的参数类型具有强制性。仅 Importing(包括INOUT)参数可以具有默认值。具有默认值的参数之后的所有 Importing 参数也必须具有默认值。

  • 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。不建议将名称用单引号引起来，并且需要大小写匹配。

  • 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()。

有关更多详细信息，请参见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(默认值)表示该函数的某些参数为 null 时将正常调用该函数。然后，函数作者有责任检查空值(如有必要)并做出适当响应。

RETURNS NULL ON NULL INPUT或STRICT表示该函数在其任何参数为 null 时始终返回 null。如果指定此参数，则当参数为空时不执行该函数；否则，该函数不执行。而是自动假定为空结果。

  • [EXTERNAL] SECURITY INVOKER
    [EXTERNAL] SECURITY DEFINER

    • SECURITY INVOKER表示该函数将以调用它的用户的特权执行。这是默认值。 SECURITY DEFINER指定要使用拥有该功能的用户的特权来执行该功能。

关键字EXTERNAL可以实现 SQL 一致性，但它是可选的，因为与 SQL 不同，此功能不仅适用于外部功能，还适用于所有功能。

  • PARALLEL

    • PARALLEL UNSAFE表示该函数不能在并行模式下执行，并且 SQL 语句中此函数的存在会强制执行串行执行计划。这是默认值。 PARALLEL RESTRICTED表示可以在并行模式下执行该功能，但是该执行仅限于并行组长。 PARALLEL SAFE表示该函数可以不受限制地并行运行。

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

  • COST execution_cost

    • 一个正数，给出该函数的估计执行成本，以cpu_operator_cost为单位。如果函数返回一个集合，则这是返回的每一行的成本。如果未指定成本，则 C 语言和内部功能的单位为 1 个单位，所有其他语言的功能的单位为 100 个单位。较大的值会使计划人员尝试避免不必要地评估函数。

  • ROWS result_rows

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

  • configuration_parameter
    value

    • SET子句导致在 Importing 功能时将指定的配置参数设置为指定的值，然后在功能退出时恢复为先前的值。 SET FROM CURRENT将执行CREATE FUNCTION时当前的参数值保存为 Importing 该功能时要应用的值。

如果将SET子句附加到函数，则在函数内针对相同变量执行的SET LOCAL命令的作用仅限于该函数：配置参数的先前值仍在函数 Export 处恢复。但是，普通的SET命令(不带LOCAL)会覆盖SET子句，就像对先前的SET LOCAL命令所做的一样：除非当前事务回滚，否则该命令的效果将在函数退出后 continue 存在。

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

  • definition

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

使用美元引号(请参见Section 4.1.2.4)来编写函数定义字符串，而不是普通的单引号语法通常会很有帮助。如果不使用美元引号，则必须通过将它们加倍来转义函数定义中的任何单引号或反斜杠。

  • obj_file, link_symbol

    • 如果 C 语言源代码中的函数名称与 SQL 函数的名称不同，则AS子句的这种形式用于可动态加载的 C 语言函数。字符串* obj_file 是包含已编译的 C 函数的共享库文件的名称，并被解释为LOAD命令。字符串 link_symbol *是函数的链接符号，即 C 语言源代码中的函数名称。如果省略链接符号，则假定它与所定义的 SQL 函数的名称相同。所有函数的 C 名称必须不同，因此必须为重载的 C 函数赋予不同的 C 名称(例如，将参数类型用作 C 名称的一部分)。

当重复CREATE FUNCTION调用引用同一目标文件时，该文件在每个会话中仅加载一次。要卸载和重新加载文件(可能在开发过程中)，请开始一个新的会话。

有关编写功能的更多信息，请参见Section 38.3。

Overloading

PostgreSQL 允许* overloading *功能；也就是说，相同的名称可以用于多个不同的函数，只要它们具有不同的 Importing 参数类型即可。无论您是否使用它，在某些用户不信任其他用户的数据库中调用函数时，此功能都需要采取安全预防措施。参见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的 precision 字段)被CREATE FUNCTION丢弃。因此，例如CREATE FUNCTION foo (varchar(10)) ...与CREATE FUNCTION foo (varchar) ...完全相同。

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

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

Examples

以下是一些简单的示例，可帮助您入门。有关更多信息和示例，请参见Section 38.3。

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

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

在 PostgreSQL 8.3 版之前，SET子句不可用，因此较早的函数可能包含相当复杂的逻辑来保存，设置和还原search_path。 SET子句更容易用于此目的。

要记住的另一点是，默认情况下，对新创建的函数，PUBLIC授予了执行特权(有关更多信息，请参见GRANT)。通常，您希望将安全定义器功能的使用限制为仅某些用户。为此，您必须撤消默认的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 版本相似，但不完全兼容。这些属性不是可移植的，不同的可用语言也不是。

为了与某些其他数据库系统兼容，可以在* argname 之前或之后写入 argmode *。但是只有第一种方法是符合标准的。

对于参数默认值，SQL 标准仅使用DEFAULT关键字指定语法。在 T-SQL 和 Firebird 中使用带有=的语法。

See Also

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE

Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:04:41