PostgreSQL存储过程(五)：函数返回值

返回值介绍：

        PostgreSQL函数提供了OUT关键字和RETURNS两种语法来实现返回值的定义，其中RETURNS关键字需要在函数体中使用RETURN关键字指明返回的变量，示例脚本如下：

1. 通过OUT关键字指定返回值
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.add(IN p_a integer,IN p_b integer,OUT p_sum integer)
    AS 
	$BODY$ 
		BEGIN
			p_sum:= p_a + p_b;  -- 进行加法运算，并将计算结果赋值给输出的参数p_sum
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
    RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null

2. 通过RETURNS关键字指定返回值
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.add(IN p_a integer,IN p_b integer)
RETURNS integer  
    AS 
	$BODY$ 
		DECLARE p_sum integer;
		BEGIN
			p_sum:= p_a + p_b;  -- 进行加法运算，并将计算结果赋值给输出的参数p_sum
			RETURN p_sum;       -- 需要指明返回的变量
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
    RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null
	

返回数据库内置数据类型：

        内置数据类型如integer、varchar、date、decimal等可以直接进行返回，脚本同上文的示例。

返回多行数据：

        需要返回多条记录时，需要使用SETOF关键字指明要返回的数据类型，示例代码如下：

1. 返回多列内置数据类型：

CREATE OR REPLACE FUNCTION mydb.mysc.get_all_data() RETURNS SETOF varchar(20) AS
$BODY$
DECLARE
    r varchar(20);
BEGIN
    FOR r IN
        SELECT cust_name FROM mydb.mysc.learn01  WHERE statistics_dt = date'2021-01-31'
    LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

执行示例：

2. 返回多行多列组合数据类型：

        需要定义返回的数据类型为数据表mydb.mysc.learn01（该表依旧使用文章三中定义的数据表）

CREATE OR REPLACE FUNCTION mydb.mysc.get_all_data1() RETURNS SETOF mydb.mysc.learn01 AS
$BODY$
DECLARE
    r mysc.learn01%rowtype; -- 设置r变量的字段和表learn01一致
BEGIN
    FOR r IN
        SELECT * FROM mydb.mysc.learn01  WHERE statistics_dt = date'2021-01-31'
    LOOP
        RETURN NEXT r; 
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

执行示例：

3. 使用TABLE返回多行多列组合数据类型：

        使用TABLE返回数据和SETOF返回功能基本相同，但TABLE方式返回组合数据类型要更加灵活，可以选择需要返回的字段，示例代码如下

CREATE OR REPLACE FUNCTION mydb.mysc.get_all_data3() 
RETURNS TABLE (cust_id varchar(20),cust_name varchar(20),aum_avg decimal(16,2),age integer,gender varchar(1)) AS
$BODY$
BEGIN
    RETURN QUERY SELECT t0.cust_id,t0.cust_name,t0.aum_avg,t0.age,t0.gender FROM mydb.mysc.learn01 t0 WHERE t0.statistics_dt = date'2021-01-31';
END;
$BODY$
LANGUAGE plpgsql;

执行示例：