描述

CREATE FUNCTION定义一个新函数。CREATE OR REPLACE FUNCTION将创建一个新函数或者替换一个现有的函数。要定义一个函数，用户必须具有该语言上的USAGE特权。

如果包括了一个模式名，那么该函数会被创建在指定的模式中。否则，它会被创建在当前模式中。新函数的名称不能匹配同一个模式中具有相同输入参数类型的任何现有函数或过程。不过，不同参数类型的函数和过程能够共享一个名字（这被称作重载）。

要替换一个现有函数的当前定义，可以使用CREATE OR REPLACE FUNCTION。但不能用这种方式更改函数的名称或者参数类型（如果尝试这样做，实际上就会创建一个新的不同的函数）。还有，CREATE OR REPLACE FUNCTION允许让你更改一个现有函数的返回类型，前提是这个函数不被依赖。

当CREATE OR REPLACE FUNCTION被用来替换一个现有的函数，该函数的拥有权和权限不会改变。所有其他的函数属性会按照该命令中所指定的或者隐含的来赋值。必须拥有（包括成为拥有角色的成员）该函数才能替换它。

如果你删除并且重建一个函数，新函数将和旧的不一样，你将必须删掉引用旧函数的现有规则、视图、触发器等。使用CREATE OR REPLACE FUNCTION更改一个函数定义不会破坏引用该函数的对象。还有，ALTER FUNCTION可以被用来更改一个现有函数的大部分辅助属性。

创建该函数的用户将成为该函数的拥有者。

要创建一个函数，你必须拥有参数类型和返回类型上的USAGE特权。

有关编写函数的详细信息，请参阅Section 38.3。

参数

重载

LightDB允许函数重载，也就是说同一个名称可以被用于多个不同的函数，只要它们具有可区分的输入参数类型。不管是否使用它，在有些用户不信任另一些用户的数据库中调用函数时，这种兼容性需要安全性的预防措施，请参考Section 11.3。

如果两个函数具有相同的名称和输入参数类型，它们被认为相同（不考虑任何OUT参数, PL/SQL函数除外，PL/SQL函数的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替换一个现有函数时，对于更改参数名是有限制的。不能更改已经分配给任何输入参数的名称（不过可以给之前没有名称的参数增加名称）。如果有多于一个输出参数，不能更改输出参数的名称，因为可能会改变描述函数结果的匿名组合类型的列名。这些限制是为了确保函数被替换时，已有的对该函数的调用不会停止工作。

如果一个被声明为STRICT的函数带有一个VARIADIC参数，会严格检查该可变数组作为一个整体是否为非空。如果该数组有空值元素，该函数仍将被调用。

示例

使用SQL函数对两个整数相加：

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函数与之前的例子不同，因为它实际返回了一个记录集合而不只是一个记录。

在 oracle 模式下，在有默认值的参数后面的参数不需要有默认值。 pl/sql:

CREATE OR REPLACE FUNCTION increment(i integer, j integer default 1, k integer) RETURNS integer AS $$
        BEGIN
                RETURN i + j + k;
        END;
$$ LANGUAGE plorasql;

select increment(1,k=>1);
 increment 
-----------
         3
(1 row)

In oracle mode, parameters types has no dependency relation with the function. pl/sql:

create table t_fp(a int, b char);
insert into t_fp(a,b) values(1,'a');
insert into t_fp(a,b) values(2,'b');
insert into t_fp(a,b) values(3,'c');
-- terminate row
insert into t_fp(a,b) values(999999,'t'); 

CREATE OR REPLACE FUNCTION f_bar(
  v_cursor ora_pkg.ref_cursor_t
)
return NUMBER
IS
declare
	total_rows int := 0;
	cursor_row t_fp%rowtype;
begin
	LOOP
		FETCH v_cursor INTO cursor_row;
			if cursor_row.a = 999999 and cursor_row.b = 't' then
				exit;
			end if;
			total_rows := total_rows+1;
			dbms_output.put_line('t_fp%rowtype (a,b) -- ' || '(' || cursor_row.a || ',' || cursor_row.b || ')' );
	END LOOP;
	dbms_output.put_line('total rows: ' || total_rows );
	return total_rows;
end f_bar;
/
WARNING:  [functioncmds]type ora_pkg.ref_cursor_t does not exist
CREATE FUNCTION

declare
v_cursor ora_pkg.ref_cursor_t;
  rt int;
begin
  open v_cursor for select * from t_fp order by a asc;
  rt=f_bar(v_cursor);
  close v_cursor;
end;
/
ERROR:  schema "ora_pkg" does not exist
LINE 2: v_cursor ora_pkg.ref_cursor_t;
                 ^
QUERY:  declare
v_cursor ora_pkg.ref_cursor_t;
  rt int;
begin
  open v_cursor for select * from t_fp order by a asc;
  rt=f_bar(v_cursor);
  close v_cursor;
end;

create or replace package ora_pkg is
  type ref_cursor_t is ref cursor;
end;
/
CREATE PACKAGE

declare
v_cursor ora_pkg.ref_cursor_t;
  rt int;
begin
  open v_cursor for select * from t_fp order by a asc;
  rt=f_bar(v_cursor);
  close v_cursor;
end;
/
t_fp%rowtype (a,b) -- (1,a)
t_fp%rowtype (a,b) -- (1,a)
t_fp%rowtype (a,b) -- (2,b)
t_fp%rowtype (a,b) -- (2,b)
t_fp%rowtype (a,b) -- (3,c)
t_fp%rowtype (a,b) -- (3,c)
total rows: 6
DO

安全地编写 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的临时表。

在LightDB 版本 8.3 之前，SET子句不可用，因而较老的函数可能包含相当复杂的逻辑来保存、设置以及恢复search_path。对于这种目的，SET子句更容易。

另一点要记住的是默认情况下，会为新创建的函数给PUBLIC授予执行特权（详见Section 6.7）。你常常会希望把安全定义器函数的使用限制在某些用户中。要这样做，你必须收回默认的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;

兼容性

SQL标准中定义了CREATE FUNCTION命令。 LightDB实现可以以兼容的方式使用，但有许多扩展。 相反，SQL标准指定了许多未在LightDB中实现的可选功能。

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

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

另见