CREATE FUNCTION

功能描述

FUNCTION（函数）是一段可重用的代码块，它接受输入参数，执行特定操作，并返回一个值或结果集。CREATE FUNCTION语句用于在数据库中创建用户自定义函数（UDF），实现DWS当前内置的系统函数中还未支持的其他计算功能，覆盖用户特定的复杂计算场景。

函数的主要特点包括：

• 支持输入参数，可根据不同条件返回结果。
• 需声明返回值的数据类型，执行结果返回单一值（标量函数）或表（表值函数）。
• 将复杂逻辑封装为单一函数，可以在SQL语句中像内置函数一样调用，提高代码的复用性。

注意事项

• 如果创建函数时参数或返回值带有精度，不进行精度检测。
• 创建函数时，函数定义中对表对象的操作建议都显式指定模式（例如hr.table1，明确指定是hr模式下面的表），否则可能会导致函数执行异常。
• 在创建函数时，函数内部通过SET语句设置current_schema和search_path无效。函数执行完的search_path和current_schema与函数执行前的search_path和current_schema保持一致。
• 如果函数参数中带有出参，SELECT调用函数必须缺省出参，CALL调用函数适配Oracle必须指定出参，对于调用重载的带有PACKAGE属性的函数，CALL调用函数可以缺省出参，具体信息参见CALL的示例。
• 兼容PostgreSQL风格的函数或者带有PACKAGE属性的函数支持重载。在指定REPLACE的时候，如果参数个数、类型、返回值有变化，不会替换原有函数，而是会建立新的函数。
• SELECT调用可以指定不同参数来进行同名函数调用。由于语法CALL适配自Oracle，因此不支持调用不带有PACKAGE属性的同名函数。
• 在创建function时，不能在avg函数外面嵌套其他agg函数，或者其他系统函数。
• 在非逻辑集群模式下，暂不支持将返回值、参数以及变量设置为建在非系统默认安装Node Group的表，sql function内部语句暂不支持对建在非系统默认安装Node Group的表操作。
• 在逻辑集群模式下，如果函数返回值和参数是用户表类型，所有涉及表都必须在同一逻辑集群内；如果函数内部涉及对多个逻辑集群表操作，函数定义时不能为IMMUTABLE和SHIPPABLE类型，以避免函数被下推到DN执行。
• 在逻辑集群模式下，函数参数、返回值不能用%type引用表字段类型，否则会导致函数创建失败。
• 新创建的函数默认会给PUBLIC授予执行权限（详见GRANT）。用户可以选择收回PUBLIC默认执行权限，然后根据需要将执行权限授予其他用户，为了避免出现新函数能被所有人访问的时间窗口，应在一个事务中创建函数并且设置函数执行权限。
• 在多个CN的集群中，不能将函数的入参或出参设置为临时表类型，因为在非当前连接CN上执行创建函数过程中，无法单独根据表名获取到正确的临时schema，进而无法获取到准确的表类型。

语法格式

• 兼容PostgreSQL风格的创建自定义函数语法。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, ...] ] ) [ RETURNS rettype [ DETERMINISTIC ] | RETURNS TABLE ( { column_name column_type } [, ...] )] LANGUAGE lang_name [ {IMMUTABLE | STABLE | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE} | WINDOW | [ NOT ] LEAKPROOF | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER} | {FENCED | NOT FENCED} | {PACKAGE} | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO | =} value | FROM CURRENT }} ][...] { AS 'definition' | AS 'obj_file', 'link_symbol' }

• Oracle风格的创建自定义函数的语法。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ] } [, ...] ] ) RETURN rettype [ DETERMINISTIC ] [ {IMMUTABLE | STABLE | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE} | {PACKAGE} | {FENCED | NOT FENCED} | [ NOT ] LEAKPROOF | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER } | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO | =} value | FROM CURRENT ][...] { IS | AS } plsql_body /

参数说明

示例：创建一个求两个整数求和的SQL函数

语法如下：

1
2
3
4
5
6
7
8
9

CREATE  OR REPLACE FUNCTION func_add_sql(
integer,                  --第一个整数参数，未命名，使用位置参数$1引用
integer                   --第二个整数参数，未命名，使用位置参数$2引用
) 
RETURNS integer              --指定函数返回类型为整数
    AS 'select $1 + $2;'     --函数体：使用位置参数$1和$2相加
    LANGUAGE SQL             --使用SQL语言实现该函数
    IMMUTABLE                --函数类型为IMMUTABLE，表示相同输入总是返回相同结果
    RETURNS NULL ON NULL INPUT;       --等同于STRICT，任何参数为NULL时，函数返回NULL

执行函数，返回两个入参的求和值：

1

SELECT func_add_sql(8,3);

执行函数，输入为Null，返回为Null。

1

SELECT func_add_sql(Null,3);

示例：创建一个处理整数自增的PL/pgSQL语言函数

语法如下：

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION func_increment_plsql(
i integer                                --输入参数，整数类型
) RETURNS integer                        --函数返回值为整数类型
AS $$
        BEGIN                             --表示函数体开始       
                RETURN i + 1;             --返回输入参数加1的结果
        END;
$$ LANGUAGE plpgsql;                     --指定函数使用PL/pgSQL语言编写

执行函数，返回整数自增结果。

1

SELECT func_increment_plsql(5); 

入参由一个表numbers的第一列指定，使用函数计算后，将计算结果返回到第二列：

1
2
3
4

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (value integer);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5);
SELECT value, func_increment_plsql(value) AS incremented_value FROM numbers ORDER BY 1;

示例：创建一个返回RECORD类型的PL/pgSQL语言函数：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE OR REPLACE FUNCTION compute(
i int,                             --输入参数，整数类型
out result_1 bigint,               --输出结果1，大范围整数类型
out result_2 bigint                --输出结果2，大范围整数类型
)
RETURNS SETOF RECORD               --结果返回一组记录
AS $$
BEGIN
    result_1 = i + 1;              --计算第一个结果值，输入值+1
    result_2 = i * 10;             --计算第二个结果值，输入值乘以10
RETURN next;                       --返回当前计算结果并继续（允许返回多行）
END;
$$LANGUAGE plpgsql;                --指定函数使用PL/pgSQL语言编写

单次调用函数，返回结果：

1

SELECT * FROM compute(5);

生成一个包含1到3的序列，并对序列的每个值调用一次compute函数。

1
2
3

SELECT i, (compute(i)).*        --先调用compute函数，再展开函数返回的所有列
FROM generate_series(1, 3)      --生成一个包含1，2，3的临时序列，用于i的赋值
AS t(i);                        --将结果作为临时表，别名为t，列名为i

与一个表test_data的数据结合使用，表中的value列作为函数compute的入参值，计算结果：

1
2
3
4
5
6

DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data (id serial, value int);
INSERT INTO test_data (value) VALUES (2), (4), (6);

SELECT id, value, (compute(value)).* 
FROM test_data ORDER BY 1;

示例：创建一个返回结果包含多个输出参数的SQL语言函数

语法如下：

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION func_dup_sql(
in int,                              --输入参数，整数类型
out f1 int,                          --输出参数1，整数类型 
out f2 text)                         --输出参数2，文本类型
 AS $$ 
SELECT $1, CAST($1 AS text) || ' is text'         --函数体，查询返回两列，第一列是输入参数$1，第二列是将参数转为文本后拼接'is text'
 $$   LANGUAGE SQL;                     

执行函数。

1

SELECT * FROM func_dup_sql(42);

示例：创建package属性的重载函数

定义了两个重载的函数，名为package_func_overload，它们位于名为package的包中。这两个函数根据参数类型的不同有不同的实现。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE OR REPLACE FUNCTION package_func_overload(
col int,                                     --输入参数1，整数类型
col2  int                                    --输入参数2，整数类型
)
RETURN integer PACKAGE
AS
DECLARE
    col_type text;                   --声明局部变量
BEGIN
     col := 122;                     --将第一个参数的值固定为122
     dbms_output.put_line('two int parameters ' || col2);
     RETURN 0;                       --返回整数值0
END;
/

CREATE OR REPLACE FUNCTION package_func_overload(
col int, 
col2 smallint
)
RETURN integer PACKAGE
AS
DECLARE
    col_type text;
BEGIN
     col := 122;
     dbms_output.put_line('two smallint parameters ' || col2);
     RETURN 0;
END;
/

函数下推逻辑及示例

函数下推逻辑分为两部分：

1. 函数体是否下推：通过函数属性（SHIPPABLE）判断。
2. 函数体内的语句是否下推：当函数体不下推的时候，函数体内的SQL语句进行独立的下推判断（优化器自动判断）。

相关链接

ALTER FUNCTION，DROP FUNCTION