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'
} ...
[ 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）允许在函数体中使用该名称。对于其他语言，就函数本身而言，输入参数的名称只是额外文档；但你可以在调用函数时使用输入参数名来提高可读性（见第 4.3 节）。无论如何，输出参数的名称很重要，因为它定义了结果行类型中的列名。（如果省略输出参数的名称，系统将选择一个默认列名。）
argtype该函数参数（如果有）的数据类型（可以是模式限定的）。参数类型可以是基础类型、复合类型或者域类型，也可以引用一个表列的类型。
根据实现语言的不同，也可能允许指定诸如
cstring这样的“伪类型”。伪类型表示实际参数类型要么没有被完整指定，要么不属于普通 SQL 数据类型集合。写成
table_name.column_name%TYPEdefault_expr如果未指定该参数，则用作默认值的表达式。该表达式必须能被强制转换为该参数的类型。只有输入参数（包括
INOUT）才能有默认值。所有跟在具有默认值参数之后的输入参数也都必须有默认值。rettype该函数的返回数据类型（可以是模式限定的）。返回类型可以是基础类型、复合类型或者域类型，也可以引用一个表列的类型。根据实现语言的不同，也可能允许指定诸如
cstring这样的“伪类型”。如果函数不应该返回值，请把返回类型指定为void。当存在
OUT或INOUT参数时，可以省略RETURNS子句。如果写出该子句，它必须与输出参数所隐含的结果类型一致：如果有多个输出参数，则为RECORD；如果只有一个输出参数，则为该输出参数的类型。SETOF修饰符表示该函数将返回一组项，而不是单个项。写成
table_name.column_name%TYPEcolumn_nameRETURNS TABLE语法中输出列的名称。这实际上是声明一个具名OUT参数的另一种方式，只不过RETURNS TABLE还隐含了RETURNS SETOF。column_typeRETURNS TABLE语法中的输出列的数据类型。lang_name实现该函数所用语言的名称。它可以是
sql、c、internal，也可以是用户定义的过程语言的名称，例如plpgsql。使用单引号括起名称已被弃用，并且要求大小写匹配。TRANSFORM { FOR TYPEtype_name} [, ... ] }列出调用该函数时应应用的转换。转换在 SQL 类型和语言相关的数据类型之间进行变换，详见 CREATE TRANSFORM。过程语言实现通常把有关内置类型的知识硬编码在代码中，因此那些不需要列举在这里。如果一种过程语言实现不知道如何处理某种类型且没有提供转换，它将回退到默认的数据类型转换行为，但这取决于具体实现。
WINDOWWINDOW表示该函数是窗口函数而不是普通函数。目前这只对用 C 编写的函数有用。在替换现有函数定义时，不能更改WINDOW属性。IMMUTABLESTABLEVOLATILE这些属性会告诉查询优化器该函数的行为。最多只能指定其中一个。如果这些属性都没有出现，则默认假定为
VOLATILE。IMMUTABLE表示该函数不能修改数据库，并且在给定相同参数值时总会返回相同结果；也就是说，它不会执行数据库查找，也不会以其他方式使用未直接出现在其参数列表中的信息。如果给出此选项，任何使用全常量参数对该函数的调用都可以立即替换为该函数值。STABLE表示该函数不能修改数据库，并且在一次表扫描内，对于相同参数值会一致地返回相同结果，但其结果可能在不同 SQL 语句之间发生变化。这适用于结果依赖于数据库查找、参数变量（例如当前时区）等的函数。（对于希望查询由当前命令修改过的行的AFTER触发器，这样做并不合适。）另请注意，current_timestamp函数族也属于稳定函数，因为它们的值在一个事务内不会变化。VOLATILE表示该函数的值即使在一次表扫描内也可能发生变化，因此无法进行任何优化。从这个意义上说，真正不稳定的数据库函数相对较少；一些例子是random()、currval()、timeofday()。但请注意，任何有副作用的函数都必须归类为不稳定，即使其结果相当可预测，也必须如此，以防其调用被优化掉；例如setval()。更多细节见第 37.6 节。
LEAKPROOFLEAKPROOF表示该函数没有副作用。除返回值外，它不会泄露其参数的任何信息。例如，对某些参数值会抛出错误而对另一些不会，或者在错误消息中包含参数值的函数，都不是防泄漏的。这会影响系统如何执行针对使用security_barrier选项创建的视图或启用了行级安全的表的查询。为了防止数据被无意暴露，系统会先强制执行安全策略和安全屏障视图中的条件，再执行查询本身中包含非防泄漏函数的用户提供条件。被标记为防泄漏的函数和操作符被视为可信，因此可以在安全策略和安全屏障视图的条件之前执行。此外，不接受参数的函数，或者没有从安全屏障视图或表中接收到任何参数的函数，即使未标记为防泄漏，也可以在安全条件之前执行。参见 CREATE VIEW 和第 40.5 节。此选项只能由超级用户设置。CALLED ON NULL INPUTRETURNS NULL ON NULL INPUTSTRICTCALLED ON NULL INPUT（默认）表示当某些参数为空值时，仍会正常调用该函数。如果有需要，则由函数作者负责检查空值并作出适当响应。RETURNS NULL ON NULL INPUT或STRICT表示只要任一参数为空值，该函数总是返回空值。如果指定了这个选项，那么在参数中出现空值时不会执行该函数，而是自动假定结果为空值。[EXTERNAL] SECURITY INVOKER[EXTERNAL] SECURITY DEFINERSECURITY INVOKER表示该函数将以调用它的用户的权限执行。这是默认设置。SECURITY DEFINER指定该函数将以其所有者用户的权限执行。为了符合 SQL，允许使用关键字
EXTERNAL。但它是可选的，因为与 SQL 不同，这个特性适用于所有函数，而不仅仅是外部函数。PARALLELPARALLEL UNSAFE表示该函数不能在并行模式中执行；SQL 语句中只要出现这类函数就会强制使用串行执行计划。这是默认选项。PARALLEL RESTRICTED表示该函数可以在并行模式中执行，但只能在并行组领导者进程中执行。PARALLEL SAFE表示该函数可以在并行模式下不受限制地执行，包括在并行工作进程中执行。如果函数修改任何数据库状态，或者通过使用子事务等方式改变事务，或者访问序列或尝试对设置进行持久更改（例如使用
setval），则应将其标记为并行不安全。如果函数访问临时表、客户端连接状态、游标、预备语句或其他系统无法在并行模式下同步的后端本地状态，则应将其标记为并行受限（例如，setseed只能由组领导者执行，因为其他进程所做的更改不会反映在领导者中）。一般来说，如果函数在受限或不安全时被标记为安全，或者实际上不安全时被标记为受限，那么在并行查询中使用它可能会抛出错误或产生错误结果。C 语言函数如果被错误标记，理论上可能表现出完全未定义的行为，因为系统无法保护自己免受任意 C 代码的影响，但在最可能的情况下，其结果不会比任何其他函数更糟。如果不确定，函数应标记为UNSAFE，这是默认值。execution_cost一个正数，给出该函数的估计执行代价，单位为 cpu_operator_cost。如果该函数返回一个集合，则这是每个返回行的代价。如果未指定代价，则对 C 语言和内部函数假定为 1 个单位，对其他所有语言的函数假定为 100 个单位。较大的值会让规划器尽量避免对该函数进行不必要的频繁求值。
result_rows一个正数，给出规划器应预计该函数返回的行数。只有函数被声明为返回集合时才允许使用此项。默认假定为 1000 行。
configuration_parametervalueSET子句会在进入函数时将指定的配置参数设为给定值，并在函数退出时恢复为先前的值。SET FROM CURRENT会把执行CREATE FUNCTION时该参数的当前值保存下来，作为进入函数时要应用的值。如果函数附带了
SET子句，那么在函数内针对同一变量执行的SET LOCAL命令，其效果会被限制在该函数内部：函数退出时，配置参数先前的值仍会被恢复。不过，普通的SET命令（不带LOCAL）会覆盖SET子句，就像它会覆盖先前的SET LOCAL命令一样：这类命令的效果会在函数退出后继续保持，除非当前事务被回滚。definition一个定义该函数的字符串常量，其含义取决于所用语言。它可以是一个内部函数名称、一个对象文件的路径、一个 SQL 命令，或者用一种过程语言编写的文本。
使用美元引用（见第 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调用引用同一个对象文件时，该文件在每个会话中只装载一次。要卸载并重新装载该文件（例如在开发期间），请启动一个新会话。attribute用于指定函数可选信息的历史方式。以下属性可以出现在此处：
isStrict等同于
STRICT或RETURNS NULL ON NULL INPUT。isCachableisCachable是IMMUTABLE的已废弃等价写法；出于向后兼容的原因，目前仍接受它。
属性名称不区分大小写。
有关编写函数的详细信息，请参阅第 37.3 节。
重载
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 类型语法来声明函数参数和返回值。不过，CREATE FUNCTION 会丢弃带圆括号的类型修饰符（例如 numeric 类型的精度字段）。因此，CREATE FUNCTION foo (varchar(10)) ...与 CREATE FUNCTION foo (varchar) ...完全等同。
在用 CREATE OR REPLACE FUNCTION 替换现有函数时，更改参数名会受到限制。不能更改已经分配给任何输入参数的名称（但可以给先前没有名称的参数补上名称）。如果输出参数多于一个，也不能更改输出参数的名称，因为那会改变描述函数结果的匿名复合类型的列名。这些限制是为了确保函数被替换时，已有的函数调用不会停止工作。
如果一个函数被声明为带有 VARIADIC 参数的 STRICT 函数，则严格性检查测试的是可变参数数组作为一个整体是否非空。如果该数组包含空值元素，仍会调用该函数。
示例
下面给出一些简单示例，帮助你开始使用。有关更多信息和示例，请参见第 37.3 节。
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
在 PL/pgSQL 中，使用参数名把一个整数加 1：
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
-- 设置一个安全的 search_path：受信的模式，然后是 '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;
兼容性
CREATE FUNCTION 命令在 SQL:1999 及更高版本中定义。PostgreSQL 版本与其相似，但并不完全兼容。这些属性和可用的不同语言都不具备可移植性。
为与某些其他数据库系统兼容，argmode 可以写在 argname 之前或之后，但只有前一种写法符合标准。
对于参数默认值，SQL 标准只规定了带有 DEFAULT 关键字的语法。带=的语法用于 T-SQL 和 Firebird。