CREATE FUNCTION

描述

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 权限， 才能创建该函数。

参数

请参阅第 35.3 节获取更多关于书写函数的信息。

重载

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）元素时，该函数仍然会被调用。

例子

这里是一些简单的例子，用于帮助你开始掌握这个命令。更多信息和例子， 参阅第 35.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
    -- 设置安全的 search_path: 信任的模式, 然后是 'pg_temp' 模式
    SET search_path = admin, pg_temp;

因为 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;

兼容性

PostgreSQL 里的版本和 SQL:1999 里的 CREATE FUNCTION 命令类似但是不完全兼容。 属性和可以使用的语言都是不可移植的。

为了和一些其它的数据库系统兼容， argmode 可以在 argname 之前或者之后写， 但是只有第一种写法是与标准兼容的。

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

又见