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

说明

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 权限。

参数说明

name

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

一个参数的模式：IN、OUT、INOUT或者VARIADIC。如果省略，默认为IN。只有OUT参数能跟在一个VARIADIC参数后面。还有，OUT和INOUT参数不能和RETURNS TABLE符号一起使用。
argname

一个参数的名称。一些语言（包括 SQL 和 PL/pgSQL）让你在函数体中使用该名称。对于其他语言，一个输入参数的名字只是额外的文字（就该函数本身所关心的来说）。但是你可以在调用一个函数时使用输入参数名来提高可读性。在任何情况下，输出参数的名称是有意义的，因为它定义了结果行类型中的列名（如果忽略一个输出参数的名称，系统将选择一个默认的列名）。
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。不推荐用单引号包围该名称，并且要求区分大小写。
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()。
LEAKPROOF

LEAKPROOF表示该函数没有副作用。它不会泄露有关其参数的信息（除了通过返回值）。例如，一个只对某些参数值抛出错误消息而对另外一些却不抛出错误的函数不是防泄漏的，一个把参数值包括在任何错误消息中的函数也不是防泄漏的。这会影响系统如何执行在使用security_barrier选项创建的视图或者开启了行级安全性的表上执行查询。对于包含有非防泄漏函数的查询，系统将在任何来自查询本身的用户提供条件之前强制来自安全策略或者安全屏障的条件，防止无意中的数据暴露。被标记为防泄漏的函数和操作符被假定是可信的，并且可以在安全性策略和安全性屏障视图的条件之前被执行。此外，没有参数的函数或者不从安全屏障视图或表传递任何参数的函数不一定要被标记为防泄漏的。详见CREATE VIEW和第 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指定要用拥有该函数的用户的特权来执行该函数。为了符合 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 行。
configuration_parameter
value

SET子句导致进入该函数时指定配置参数将被设置为指定值。并且在该函数退出时恢复到该参数之前的值。SET FROM CURRENT会把CREATE FUNCTION被执行时该参数的当前值保存为进入该函数时将被应用的值。如果一个SET子句被附加到一个函数，那么在该函数内为同一个变量执行的SET LOCAL命令会被限制于该函数：在函数退出时该配置参数之前的值仍会被恢复。不过，一个普通的SET命令（没有LOCAL）会覆盖SET子句，更像一个之前的SET LOCAL命令所做的那样：这种命令的效果在函数退出后将会持续，除非当前事务被回滚。
definition

一个定义该函数的字符串常量，其含义取决于语言。它可以是一个内部函数名、一个对象文件的路径、一个 SQL 命令或者用一种过程语言编写的文本。
obj_file, link_symbol

当 C 语言源代码中该函数的名称与 SQL 函数的名称不同时，这种形式的AS子句被用于动态可载入 C 语言函数。字符串obj_file是包含编译好的C函数的动态库文件的名称，它会由LOAD命令解析。字符串link_symbol是该函数的链接符号，也就是该函数在 C 语言源代码中的名称。如果省略链接符号，它将被假定为要定义的 SQL 函数的名称。所有函数的C名称都必须不同，因此必须为重载的C函数给出不同的C名称（例如把参数类型作为C名称的一部分）。在重复调用引用同一对象文件的CREATE FUNCTION时，对每个会话该文件只会被载入一次。要卸载并且重新装载该文件（可能是在开发期间），需要开始一个新会话。

重载

Vastbase E100允许函数重载;也就是说，只要具有不同的输入参数说明类型，相同的名称就可以用于几个不同的函数。无论您是否使用它，此功能都需要在数据库中调用函数时采取安全预防措施，其中一些用户不信任其他用户。

如果两个函数具有相同的名称和输入参数说明类型，则忽略任何 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，则严格性检查将测试可变参数说明数组作为整数是否为非空。如果数组具有null元素，则仍将调用该函数。

示例

以下是一些帮助您入门的简单示例。

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设置为排除不受信任的用户可写的任何模式。这可以防止恶意用户创建掩盖该功能要使用的对象的对象（例如，表，函数和操作符）。在这方面特别重要的是临时表schema，默认情况下首先搜索，通常可由任何人写入。通过强制最后搜索临时模式可以获得安全的安排。要执行此操作，请将 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
    -- 设置一个安全的 search_path：受信的模式，然后是 'pg_temp'。
    SET search_path = admin, pg_temp;

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

另外要记住的一点是，默认情况下，对于新创建的函数，执行权限被授予 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;

VastbaseE100

CREATE FUNCTION

语法格式

说明

参数说明

重载

注意事项

示例

安全地编写SECURITY DEFINER功能

弹框头部