CREATE FUNCTION · PostgreSQL 中文文档 9.3

# CREATE FUNCTION ## Name 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_ | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST _execution_cost_ | ROWS _result_rows_ | SET _configuration_parameter_ { TO _value_ | = _value_ | FROM CURRENT } | AS '_definition_' | AS '_obj_file_', '_link_symbol_' } ... [ WITH ( _attribute_ [, ...] ) ] ``` ## 描述 `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)允许你在函数体里使用参数名字。对于其它语言，输入参数名字只是额外的文档，这只是就函数定义本身而言的；在调用函数时你可以使用输入参数名字来提高可读性。（见 [Section 4.3](#calibre_link-733)）。无论如何，输出参数的名字是非常重要的，因为它定义了结果行类型的列名。（如果你省略了输出参数的名字，那么系统会自动选择一个缺省的列名。） `_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`，或者是用户定义的过程语言名字。该名字可以用单引号包围。 `WINDOW` `WINDOW` 表示该函数不是普通函数而是一个_窗口函数_。这个属性当前仅对用C写的函数起作用。当替换已有函数定义时不能改变函数的`WINDOW`属性。 `IMMUTABLE``STABLE` `VOLATILE` 这些属性是在查询优化器中用来优化函数的调用的，只能指定一个。缺省值是 `VOLATILE`。 `IMMUTABLE` 表示该函数不能修改数据库，并且在给出同样的参数值时总是返回同样的结果；也就是说，它不查询数据库或者只使用那些没有出现在参数列表里的信息。如果给出这个选项，那么任何全部使用常数对该函数的调用都将立即替换为该函数的值。 `STABLE` 表示该函数不能修改数据库，对相同参数值，在同一次表扫描里，该函数的返回值不变，但是返回值可能在不同 SQL 语句之间变化。这个选项对那些结果依赖数据库查找、参数变量(比如当前时区)之类的函数很合适。（但对于那些希望查询当前命令修改的行的 `AFTER` 类型的触发器是不合适的。）还要注意 `current_timestamp` 函数族是稳定的，因为它们的值在一次事务中不会变化。 `VOLATILE` 表示该函数值甚至可以在一次表扫描内改变，因此不会做任何优化。只有很少的数据库函数在这个概念上是易变的；一些例子是 `random()`, `currval()`, `timeofday()`。请注意任何有副作用的函数都必需列为易变类，即使其结果相当有规律也应该这样，这样才能避免它被优化；一个例子就是 `setval()`。更多细节，请参阅[Section 35.6](#calibre_link-734)。 `LEAKPROOF` `LEAKPROOF` 表示该函数没有涉密方面的副作用。它除了返回值外，不会泄露它的参数的任何信息。例如，一个函数抛出了一个参数不正确的错误，但错误消息里包含了参数值的信息，那这个函数就是不保密的(leakproof)。查询计划生成器可以把保密的函数放到用 `security_barrier` 选项生成的视图中，而不保密的函数则不可以。参见 [CREATE VIEW](#calibre_link-473) 和 [Section 38.5](#calibre_link-480)。这个选项只能由超级用户设置。 `CALLED ON NULL INPUT` `RETURNS NULL ON NULL INPUT` `STRICT` `CALLED ON NULL INPUT` (缺省)表明该函数在自己的某些参数是 NULL 的时候还是可以按照正常的方式调用。函数的作者必须负责检查 NULL 以及进行相应地处理。 `RETURNS NULL ON NULL INPUT` 或 `STRICT` 表明如果它的任何参数是 NULL，此函数总是返回 NULL。如果声明了这个参数，则如果存在 NULL 参数时不会执行该函数；而只是自动假设一个 NULL 结果。 `[EXTERNAL] SECURITY INVOKER` `[EXTERNAL] SECURITY DEFINER` `SECURITY INVOKER` (缺省)表明该函数将使用调用它的用户权限执行。 `SECURITY DEFINER` 声明该函数将以创建它的用户的权限执行。关键字 `EXTERNAL` 的目的是和 SQL 兼容，它是可选的，因为这个特性适用于所有函数，而不仅仅在SQL中的外部函数。 The key word is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not only external ones. `_execution_cost_` 一个正数，表示以 [cpu_operator_cost](#calibre_link-735) 为单位的该函数的估算的执行代价。如果函数返回的是一个结果集，它表示的是每行结果的代价。如果这个选项没有指定，对于用C语言函数和内部函数缺省值为 1，而其他语言定义的函数缺省值为 100。较大的值会使查询计划生成器在不必要的情况下尽量避免调用该函数。 `_result_rows_` 一个正数给出了查询计划生成器预期的该函数返回的估算的结果集行数，它仅允许返回结果集的函数指定。缺省值为 1000。 `_configuration_parameter_` `_value_` `SET` 语句能在进入函数时将某个配置参数设置成指定的值，并且当函数返回时恢复成之前的值。`SET FROM CURRENT` 使用会话当前的该参数值做为进入函数时的该参数的值。如果一个函数创建时带有某个配置参数的`SET` 语句，那么在函数内对于同一参数的`SET LOCAL` 语句的作用域仅限于该函数：执行函数之前的该参数的值会在函数返回后恢复。然而一个一般的同一参数的 `SET` 语句（不带 `LOCAL`）的作用效果将在函数返回后继续保持，直到当前的事务回滚。关于允许设置的参数和值的进一步信息请参考 [SET](#calibre_link-675) 和 [Chapter 18](#calibre_link-500)。 `_definition_` 一个定义函数的字符串常量，含义取决于语言。它可以是一个内部函数名字、一个指向某个目标文件的路径、一个 SQL 查询、一个过程语言文本。在写函数体字符串时经常使用美元符引用语法（见[Section 4.1.2.4](#calibre_link-736)），而不通常的单引号语法，这是因为如果不使用美元符引用语法，在函数定义中出现的任何单引号和反斜线都需要前面再加一个相应的相同字符来转义。 `_obj_file_`, `_link_symbol_` 这个形式的 `AS` 子句用于在函数的 C 源文件里的名字和 SQL 名字不同时可动态加载 C 语言函数。字符串 `_obj_file_` 是包含可动态加载对象的文件名，而 `_link_symbol_` 是函数的链接符号，也就是该函数在 C 源文件里的名字。如果省略了链接符号，那么就假设它和被定义的 SQL 函数同名。当使用同一个可动态加载文件重复执行命令 `CREATE FUNCTION` 生成函数时，在一个全会话里该文件只会被加载一次。若想卸载或重新加载该文件（通常在开发过程中使用），可以开启一个新的会话。 `_attribute_` 历史遗留的函数可选信息。下面的属性可以在此出现： `isStrict` 等效于 `STRICT` 或 `RETURNS NULL ON NULL INPUT`。 `isCachable` `isCachable` 是 `IMMUTABLE` 的过时的等效语法；不过出于向下兼容，仍然接受它。属性名是大小写无关的。请参阅[Section 35.3](#calibre_link-737)获取更多关于书写函数的信息。 ## 重载 PostgreSQL 允许函数_重载_；也就是只要输入参数不同，几个不同的函数可以同名。不过，所有函数的 C 名字必须不同，也就是说你必须给予重载的 C 函数不同的 C 名字(比如，使用参数类型作为 C 名字的一部分)。如果两个函数同名，并且_输入_参数类型也相同，那么就认为这两个函数是一样的，忽略所有 `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 OR REPLACE FUNCTION` 替换一个已存在函数的定义时，改变函数的参数名字有一些限制。你不能对已使用了名字的输入参数改名（但是你可以给没有使用名字的输入参数加一个名字）。如果该函数有超过一个输出参数，你也不能改变输出参数的名字，因为改名会改变用来描述函数返回结果的匿名复合类型的列名。这些限制都是为了使在替换函数的定义后对该函数的已有调用还能正常工作。如果一个函数声名为 `STRICT`，并带有一个 `VARIADIC` 模式的参数，参数的严格性检查将可变数组_整体_看作非空的（non-null）。所以当调用时参数数组有空（null）元素时，该函数仍然会被调用。 ## 例子这里是一些简单的例子，用于帮助你开始掌握这个命令。更多信息和例子，参阅[Section 35.3](#calibre_link-737)。 ``` 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](#calibre_link-738) 排除可以被任何不信任用户更改的模式。这能避免恶意用户用生成的对象来替代函数中用到的对象的攻击方法。特别应该注意的是临时表模式，该模式默认在搜索路径中排在第一个，并且对任何用户可写。为安全考虑可以强制临时表模式最后被搜索。为了达到这个目的，可以将 `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; ``` 因为 PostgreSQL 8.3 版本没有 `SET` 选项，所以老函数可以包含比较复杂的逻辑去存储，设置和恢复 `search_path` 参数，而使用 `SET` 选项则简单的多。另一个需要注意的是：默认新创建的函数的执行权限被授予了 `PUBLIC`，（更多信息见[GRANT](#calibre_link-19)）。更常见的是你希望限制安全函数只给某些用户使用。为了实现这个目的，你必须收回默认的给 `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; ``` ## 兼容性 PostgreSQL 里的版本和 SQL:1999 里的 `CREATE FUNCTION` 命令类似但是不完全兼容。属性和可以使用的语言都是不可移植的。为了和一些其它的数据库系统兼容， `_argmode_` 可以在 `_argname_` 之前或者之后写，但是只有第一种写法是与标准兼容的。对于默认参数，SQL标准仅仅规定了使用 `DEFAULT` 关键字的语法。在T-SQL 和 Firebird 中，也使用 `=` 的语法。 ## 参见 [ALTER FUNCTION](#calibre_link-588), [DROP FUNCTION](#calibre_link-587), [GRANT](#calibre_link-19), [LOAD](#calibre_link-739), [REVOKE](#calibre_link-20), [createlang](#calibre_link-47)