GAUSSDB-函数

语法格式

兼容PostgreSQL语法

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语法

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
/

参数详细说明

OR REPLACE
如果函数已存在，则重新定义。

function_name
要创建的函数名字（可以用模式修饰）。
取值范围：字符串，要符合标识符的命名规范。
说明：
如果创建的函数名与系统函数同名，建议指定schema。调用自定义函数时需指定schema，否则系统会优先调用系统函数。

argname
函数参数的名字。
取值范围：字符串，要符合标识符的命名规范。

argmode
函数参数的模式。
取值范围：IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数后面能跟VARIADIC。并且OUT和INOUT模式的参数不能用在RETURNS TABLE的函数定义中。

说明：
VARIADIC用于声明数组类型的参数。

argtype
函数参数的类型。

expression
函数参数的默认表达式。

rettype
函数返回值的数据类型。

如果存在OUT或IN OUT参数，可以省略RETURNS子句。如果存在，该子句必须和输出参数所表示的结果类型一致：如果有多个输出参数，则为RECORD，否则与单个输出参数的类型相同。

SETOF修饰词表示该函数将返回一个集合，而不是单独一项。

DETERMINISTIC
为适配Oracle SQL语法，未实现功能，不推荐使用。

column_name
字段名称。

column_type
字段类型。

definition
一个定义函数的字符串常量，含义取决于语言。它可以是一个内部函数名字、一个指向某个目标文件的路径、一个SQL查询、一个过程语言文本。

LANGUAGE lang_name
用以实现函数的语言的名字。可以是SQL，internal，或者是用户定义的过程语言名字。为了保证向下兼容，该名字可以用单引号（包围）。若采用单引号，则引号内必须为大写。

WINDOW
表示该函数是窗口函数，替换函数定义时不能改变WINDOW属性。

须知：
自定义窗口函数只支持LANGUAGE是internal，并且引用的内部函数必须是窗口函数。

IMMUTABLE
表示该函数在给出同样的参数值时总是返回同样的结果。

如果函数的入参是常量，会在优化器阶段计算该函数的值。益处是可以尽早获取表达式的值，从而能更准确的进行代价估算，生成的执行计划也更优。

用户自定义的IMMUTABLE的函数是会被自动下推到DN执行的，但是这样可能有潜在的风险，即如果用户错误定义了函数的IMMUTABLE属性，但是函数执行的过程并不是IMMUTABLE的，那么可能会导致结果错误等严重问题。因此，用户在指定函数的属性为IMMUTABLE的时候，要特别慎重。

举例如下：
如果自定义函数中引用了表，视图等对象，那么该函数就不能定义为IMMUTABLE，因为当表的数据发生变化的时候，函数的返回值可能发生变化。
如果自定义函数中引用了STABLE/VOALATILE类型的函数，那么该函数不能定义为IMMUTABLE。
如果自定义函数中有不下推的因素，则该函数不能定义成IMMUTABLE，因为IMMUTABLE意味着要下推到DN执行，与函数内部的不下推因素相互冲突。典型场景例如，包含不下推的函数、语法等。
如果自定义函数中含有聚合运算，但聚合运算的运算需要生成STREAM计划才能完成计算的（部分结果在DN计算，部分结果在CN计算，例如listagg函数等）。
同时，为了防止这种情况下可能出现严重问题，数据库内部可以通过设置behavior_compat_options=‘check_function_conflicts’来开启对函数定义冲突的检查，目前可以识别出上述1和2场景。

STABLE
表示该函数不能修改数据库，对相同参数值，在同一次表扫描里，该函数的返回值不变，但是返回值可能在不同SQL语句之间变化。

VOLATILE
表示该函数值可以在一次表扫描内改变，因此不会做任何优化。

SHIPPABLE
NOT SHIPPABLE

表示该函数是否可以下推到DN上执行。

对于IMMUTABLE类型的函数，函数始终可以下推到DN上执行。
对于STABLE/VOLATILE类型的函数，仅当函数的属性是SHIPPABLE的时候，函数可以下推到DN执行。
用户在定义函数的SHIPPABLE属性时也需特别慎重，SHIPPABLE意味着整个函数会下推到DN上执行，如果设置不当，会导致结果错误等严重问题。

与定义IMMUTABLE属性一样，SHIPPABLE属性的定义也有诸多约束，简单来说就是函数内部不能有不可下推的因素，函数下推到单DN执行后，函数内部的计算逻辑仅依赖当前DN的数据集合。

举例如下：
如果函数内部引用了表，并且表为HASH分布，那么该函数通常不能定义为SHIPPABLE。
函数内部有不可下推的因素，函数，语法等，那么该函数不能定义为SHIPPABLE，可参考语句下推调优。
函数内部的计算过程可能需要跨DN数据，这种情况该函数通常不能定义为SHIPPABLE，例如一些聚合运算等。
PACKAGE
表示该函数是否支持重载。PostgreSQL风格的函数本身就支持重载，此参数主要是针对Oracle风格的函数。
不允许package函数和非package函数重载或者替换。
package函数不支持VARIADIC类型的参数。
不允许修改函数的package属性。
LEAKPROOF
指出该函数的参数只包括返回值。LEAKPROOF只能由系统管理员设置。

CALLED ON NULL INPUT
表明该函数的某些参数是NULL的时候可以按照正常的方式调用。该参数可以省略。

RETURNS NULL ON NULL INPUT
STRICT

STRICT用于指定如果函数的某个参数是NULL，此函数总是返回NULL。如果声明了这个参数，当有NULL值参数时该函数不会被执行；而只是自动返回一个NULL结果。

RETURNS NULL ON NULL INPUT和STRICT的功能相同。

EXTERNAL
目的是和SQL兼容，是可选的，这个特性适合于所有函数，而不仅是外部函数。

SECURITY INVOKER
AUTHID CURRENT_USER

表明该函数将带着调用它的用户的权限执行。该参数可以省略。

SECURITY INVOKER和AUTHID CURRENT_USER的功能相同。

SECURITY DEFINER
AUTHID DEFINER

声明该函数将以创建它的用户的权限执行。

AUTHID DEFINER和SECURITY DEFINER的功能相同。

FENCED
NOT FENCED

该函数只对用户定义的C函数生效，声明函数是在保护模式还是非保护模式下执行。如果函数声明为NOT FENCED模式，则函数的执行在CN或者DN进程中进行。如果函数声明为FENCED模式，则函数在新fork的进程执行，这样函数的异常不会影响CN或者DN进程。

FENCED/NOT FENCED模式的选择：

正在开发或者调试的Function使用FENCED模式。开发测试完成，使用NOT FENCED模式执行，减少fork进程以及通信的开销。
复杂的操作系统操作，例：打开文件，信号处理，线程处理等操作，使用FENCED模式。否则可能影响GaussDB(DWS)数据库的执行。
默认值为FENCED。
COST execution_cost
用来估计函数的执行成本。

execution_cost以cpu_operator_cost为单位。
取值范围：正数

ROWS result_rows
估计函数返回的行数。用于函数返回的是一个集合。
取值范围：正数，默认值是1000行。

configuration_parameter
value
把指定的数据库会话参数值设置为给定的值。如果value是DEFAULT或者RESET，则在新的会话中使用系统的缺省设置。OFF关闭设置。
取值范围：字符串

DEFAULT
OFF
RESET
指定默认值。

from current
取当前会话中的值设置为configuration_parameter的值。

plsql_body
PL/SQL存储过程体。

示例

编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常ID，如果不是则显示为id2

【方法1】
create or replace function add_mask(id1 varchar(200),id2 varchar(200)) returns varchar(200)
as 
$$
begin
	if id1=current_user then --current_user 当前登录用户名
		return id1;
	else
		return id2;
	end if;
END;
$$ language plpgsql;
【方法2】
create or replace function add_mask(id1 varchar(200),id2 varchar(200)) returns varchar(200)
as 
$$
begin
	return (case when current_user=id1 then id1 else id2 end);
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION func_jd(id1 int,km varchar(20)) RETURNS decimal(10,1)
AS 
$$
DECLARE point decimal(10,1);
BEGIN 
	IF km='math' THEN 
	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN math<=69 THEN 0.1
 	  WHEN math<=79 THEN 0.2
 	  WHEN math<=89 THEN 0.3
 	  WHEN math<=100 THEN 0.4
 	  ELSE 0
 	  END AS math_p INTO point FROM stu WHERE id=id1;
 	ELSEIF km='art' THEN 
 	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN art<=69 THEN 0.1
 	  WHEN art<=79 THEN 0.2
 	  WHEN art<=89 THEN 0.3
 	  WHEN art<=100 THEN 0.4
 	  ELSE 0
 	  END AS art_p INTO point FROM stu WHERE id=id1;
 	ELSEIF km='phy' THEN 
 	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN phy<=69 THEN 0.1
 	  WHEN phy<=79 THEN 0.2
 	  WHEN phy<=89 THEN 0.3
 	  WHEN phy<=100 THEN 0.4
 	  ELSE 0
 	  END AS phy_p INTO point FROM stu WHERE id=id1;
	END IF;
	RETURN point;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func_jd(id1 int,km varchar(20)) RETURNS decimal(10,1)
AS 
$$
DECLARE point decimal(10,1);
BEGIN 
	IF km='math' THEN 
	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN math<=69 THEN 0.1
 	  WHEN math<=79 THEN 0.2
 	  WHEN math<=89 THEN 0.3
 	  WHEN math<=100 THEN 0.4
 	  ELSE 0
 	  END AS math_p INTO point FROM stu WHERE id=id1;
 	ELSEIF km='art' THEN 
 	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN art<=69 THEN 0.1
 	  WHEN art<=79 THEN 0.2
 	  WHEN art<=89 THEN 0.3
 	  WHEN art<=100 THEN 0.4
 	  ELSE 0
 	  END AS art_p INTO point FROM stu WHERE id=id1;
 	ELSEIF km='phy' THEN 
 	SELECT  CASE WHEN math<=59 THEN 0
 	  WHEN phy<=69 THEN 0.1
 	  WHEN phy<=79 THEN 0.2
 	  WHEN phy<=89 THEN 0.3
 	  WHEN phy<=100 THEN 0.4
 	  ELSE 0
 	  END AS phy_p INTO point FROM stu WHERE id=id1;
	END IF;
	RETURN point;
END;
$$
LANGUAGE plpgsql;


create table stu1(id varchar(10),name varchar(10));
insert into stu1 values ('0001','name1'),('0002','name2'),('0003','name3'),('0004','name4');

编写一个函数，第一个值是要选择的id，第二个值是其他id要被替换成的值。
比如查表时当id=0001，其他id被替换成****，name列不变。
  id  | name  
------+-------
 0001 | name1
 **** | name2
 **** | name3
 **** | name4
比如当id=0002要查表时，显示如下
  id  | name  
------+-------
 **** | name1
 0002 | name2
 **** | name3
 **** | name4


create or replace function fun_return_next(vid varchar,vpw varchar) returns setof stu1 
 as $$
 	declare r stu1%ROWTYPE;
 	begin
 	for r in (select (case when id=vid then id else vpw end) as nid,name from stu1) loop
 		return next r;
 		end loop;
 		return ;
	end
 $$ language plpgsql;
 
 编写函数，实现银行家舍入算法，传入2个参数
--参数1：需要取小数点精度的数值
--参数2：指小数点后取多少位

银行家舍入算法逻辑：四舍六入五成双，五后有数就进一，五后无数看五前，五前为偶应舍弃，五前为奇要进一

CREATE OR REPLACE FUNCTION Banker_Rounding(num numeric, i integer) 
RETURNS numeric AS
$$ 
DECLARE 
result numeric; 
BEGIN 
 SELECT 
	CASE  -- 当舍入到指定小数位后的数与原数之差乘以10的(i+1)次方等于5时，进行银行家舍入
		WHEN abs(num-round(num,i))*10^(i+1)=5 
		 -- 如果舍入后的数的最后一位是奇数，则向下舍入
		THEN round(num,i)-(right(round(num,i),1)%2)*0.1^i 
	ELSE  -- 否则直接进行四舍五入
		round(num,i) 
	END 
 INTO result; 
RETURN result; 
END; 
$$
language plpgsql

--编写函数FUNC_SUM,根据传递的学生的学生编号或者姓名返回某个学生的分数总和
-- 考生作答
create or replace function FUNC_SUM(sno1 int default null ,sname1 varchar(50) default null) returns int as $$
declare sum_grade int;
begin
	--判断学号是否存在
	case when sno1 is null and sname1 is null then 
	RAISE EXCEPTION '错误：请输入学号或者学生姓名';
	when sno1 is not null and (select sno from student where sno=sno1) is null then 
	RAISE EXCEPTION '错误：学生编号 % 不存在。', sno1;
	--判断学生姓名是否存在
	when sname1 is not null and (select sname from student where sname=sname1) is null then 
	RAISE EXCEPTION '错误：学生姓名 % 不存在。', sname1;
	--判断学号和姓名是否对应
	--when sno1 is not null and sname1 is not null and sno1 <> (select sno from student where sname=sname1) 
	when sno1 is not null and sname1 is not null and sname1 <> (select sname from student where sno=sno1) 
	then 
	RAISE EXCEPTION '错误：学生编号 % 和学生姓名 % 不是同一个学生。' ,sno1,sname1;
	else 
	select sum(grade) into sum_grade from (select s.sno,s.sname,e.grade from student s,elective e where s.sno=e.sno) where sno=sno1 or sname=sname1;
	end case;
return sum_grade;
---RETURN COALESCE(sum_grade, 0); --如果是null,返回0
end;
$$language plpgsql;
--两个参数，可以都输入，也可以只输入一个。
--两个参数都输入
select func_sum(1,'a');
call func_sum(1,'a');
--只输入学号，姓名默认为空
select func_sum(1);
call func_sum(1);
--只输入姓名，学号默认为空。
--因为是第二个参数，需要指定参数名
select func_sum(sname1:='a');
select func_sum(sname1=>'a');
--或者第一个参数传入null
select func_sum(null,'a');