OpenGauss 函数调研

调研：函数

pgSQL语言函数介绍

PL/pgSQL是一种可载入的过程语言，openGauss实际上就是在postgre数据库的基础上，添加了自己的通信和安全协议，形成的关系型数据库，在许多地方和传统的关系型数据库Postgre十分相像；

在PostgreSQL中，函数是可以执行特定任务的可重用代码块，能够接受参数输入、处理数据并返回结果。这些函数可以用于简化复杂的数据处理操作，提高代码的可读性和可维护性。

用PL/pgSQL创建的函数可以被用在任何可以使用内建函数的地方。

例如，可以创建复杂条件的计算函数并且后面用它们来定义操作符或把它们用于索引表达式。

引入函数的目的

在SQL最初的设计中，这种语言应该是高度非过程化的，这句话的意思是开发者无需关心具体的操作步骤，而应该专注与其查询和更新操作，随着数据库业务拓展，引入函数这种过程化的元素应该有诸多考虑：

复杂数据处理和扩展性：适应更广阔的业务场景
代码重用，安全性和高级封装：减少开发者的压力，提供API，为上层提供服务
事务管理，触发器和事件：胜任更复杂的数据库管理任务
高级语言支持和扩展性：支持PL/pgSQL、PL/Python、PL/c
减少通信开销：避免对结果的多轮传送和多轮查询解析，实现业务逻辑的集中管理

简单示例解析

以下是一个在openGauss中实现两数之和的函数

CREATE FUNCTION add_two_numbers(a integer, b integer) 
RETURNS integer AS 
$ BEGIN
  RETURN a + b;
END;$ 
LANGUAGE plpgsql;

观察语法高亮部分，不难看出有以下关键字可能比较重要：

CREATE FUNCTION :和存储过程类似，需要有函数的创建声明关键字
RETURNS AS:和大部分高级语言类似，需要有函数的输入参数和对返回值的类型声明；
BEGIN END:和事务类似，定义函数体；
RETURN:函数的返回值
LANGUAGE:创建函数的语言

函数语法

函数创建

应用PL/pgSQL创建函数的语法为CREATE FUNCTION。PL/pgSQL是一种可载入的过程语言。

其应用方法与存储过程相似，只是存储过程无返回值，函数有返回值。

函数体

PL/pgSQL用于创建函数时，函数体是以字符串的形式存在的，比如'function body text'

CREATE FUNCTION somefunc(integer, text) RETURNS integer 
LANGUAGE plpgsql AS 
'function body text';

CREATE FUNCTION somefunc(integer, text) RETURNS integer 
LANGUAGE plpgsql AS 
$
function body text
$;

由于函数体是字符串，所以其中的单引号或者反斜线都要通过双写来转义，这可能会导致可读性变差，我们可以使用$符号引用的方式来改善这种情况。

一个$符号引用的字符串常量的组成

由一个$符号
(可选的)标签名
另一个$符号
字符串内容
一个$符号
上面相同的标签
另一个$符号组成。

例如

$  functionbody  $
$Tag$  functionbody $Tag$

这里标签的作用：

标识一个块以便在一个EXIT语句中使用
标识在该块中声明的变量名

函数块

PL/pgSQL是一种块结构的语言。一个函数体的完整文本必须是一个块，它的语法结构为：

[ <<label>> ]
[ DECLARE 一些声明 ]
BEGIN
    [一些操作...]
    RETURN ...;
END [ label ];

这是函数块的结构，注意在END后和RETURN应有分号;

函数的每一条语句后都应该有分号；

声明和初始化往往遵循着以下的规则

<name> <type> [:= value];
a integer := 10;  -- 声明整数变量a，初始化为10
b integer;       -- 声明一个整数变量b

操作一般也和高级语言类似

比如赋值操作

b := a+20;

调试打印

RAISE NOTICE
RAISE DEBUG

控制结构

IF <条件> THEN
	-- 代码
ELSIF <条件> THEN
	-- 代码
ELSE 
	-- 代码
END IF;

循环结构

LOOP
    -- 代码
    EXIT WHEN <条件>;
END LOOP;

WHILE <条件> LOOP
    -- 代码
END LOOP;

FOR i IN 1..rows LOOP
    -- 代码
END LOOP;

抛出异常

BEGIN
    -- 代码
EXCEPTION
    WHEN <exception_type> THEN
        -- 异常处理代码
END;

嵌套

一个块的语句节中的任何语句可以是一个子块。

子块可以被用来逻辑分组或者将变量局部化为语句的一个小组。在子块的持续期间，在一个子块中声明的变量会掩盖外层块中相同名称的变量，这点和高级语言中的作用域类似；

但是如果你用块的标签限定外层变量的名字，你仍然可以访问它们。一个嵌套的示例如下：

CREATE FUNCTION somefunc() RETURNS integer AS $
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    
    -- 创建一个子块
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;


    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END outerblock;
$ LANGUAGE plpgsql;

上述的函数具有两个块，其中变量quantity的值在外层始终为50没有发生变化；

不要把PL/pgSQL中用来分组语句的BEGIN/END与用于事务控制的同名SQL命令弄混。PL/pgSQL的BEGIN/END只用于分组，它们不会开始或结束一个事务。

总结

一个完整的语法结构如下：

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}
        | [ NOT  ] LEAKPROOF
        | WINDOW
        | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
        | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER | AU
THID DEFINER  | AUTHID CURRENT_USER}
        | {FENCED | NOT FENCED}
        | {PACKAGE}
        | COST execution_cost
        | ROWS result_rows
        | SET configuration_parameter { {TO | =} value | FROM CURRENT }
        | COMMENT 'text' 
        | pipelined_clause
    ] 
    [...]
    {
        AS 'definition'
        | AS 'obj_file', 'link_symbol'
    }

OpenGauss场景运用函数

现在大家学会了如何在PgSQL数据库中如何使用函数写出两数之和了，我们来看看在OpenGauss场景下函数有怎么样的运用吧；

我们关注作业中的表，如下

（查看数据库中的所有表）

\dt

这些表的具体内容如下：

案例1：Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

简要翻译：1980年前电影平均分的平均值和1980年后电影平均分的平均值之差

CREATE OR REPLACE FUNCTION avg_rating_difference_before_after_1980()
RETURNS numeric AS $
DECLARE
    avg_before numeric;
    avg_after numeric;
    result numeric;
BEGIN 
    WITH avg_ratings_before AS (
        SELECT m.mID, AVG(r.stars) AS avg_rating
        FROM Movie m
        JOIN Rating r ON m.mID = r.mID
        WHERE m.year < 1980
        GROUP BY m.mID
    ) 
    SELECT AVG(avg_rating) INTO avg_before
    FROM avg_ratings_before;
 
    WITH avg_ratings_after AS (
        SELECT m.mID, AVG(r.stars) AS avg_rating
        FROM Movie m
        JOIN Rating r ON m.mID = r.mID
        WHERE m.year >= 1980
        GROUP BY m.mID
    ) 
    SELECT AVG(avg_rating) INTO avg_after
    FROM avg_ratings_after;
 
    result := avg_before - avg_after;

    RETURN result;
END;
$ LANGUAGE plpgsql;

调用函数我们截图获取结果

SELECT avg_rating_difference_before_after_1980();

手动计算发现，或者使用其他SQL语句发现，确实是正确的结果；

课题2-openGauss + SQL