PostgreSQL如何自定义函数（一）

crown
2,656 阅读3分钟

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第2天，点击查看活动详情。

引言

PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，循环，判断，控制流程等)。它能一次编译多次调用，减少应用程序与数据库服务器的网络通信开销，从而提升整体性能，自定义函数在BI系统中尤为重要。

函数格式

CREATE OR REPLACE FUNCTION "public"."函数名"("参数名1" 参数类型,"参数名2" 参数类型)
  RETURNS "返回类型模式"."返回类型" AS `$BODY$`
DECLARE 
    定义变量
--函数逻辑
BEGIN
......
RETURN 返回值;
--抛出异常的时走的逻辑
EXCEPTION
    WHEN OTHERS THEN
        RETURN '';
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

1、在 CREATE FUNCTION 语句后面给定函数的名称
2、在函数名称后面的圆括号内放置逗号分隔的参数列表
3、在 RETURNS 关键字之后指定函数的返回类型模式和返回类型
4、将代码放在 BEGIN 和 END 块内。该函数始终以分号（;）结尾，紧跟在END关键字之后
5、说明函数的过程语言。比如，使用 plpgsql 表示 PL/pgSQL

参数模式

image.png 如上图，我们使用Navicat工具新建函数的时候会弹出函数向导，参数模式有三种可供选择
IN：默认情况下，PostgreSQL 任何参数都是 IN 参数。你可以将 IN 参数传递给函数，但无法从返回结果里再获取到。
举例：

--IN可以省略
CREATE OR REPLACE FUNCTION "public"."get_sum"(IN "a" NUMERIC,IN "b" NUMERIC)
  RETURNS "pg_catalog"."NUMERIC" AS `$BODY$`
DECLARE 
    c NUMERIC;

BEGIN
    c := a+b 
    RETURN c;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

OUT：OUT 参数是函数参数列表的一部分，你可以将其作为结果的一部分返回。如果使用 OUT 参数，则不需要 RETURN 语句。OUT 参数经常用于一个函数需要返回多个值，而又不需要自定义数据类型。
举例：

CREATE OR REPLACE FUNCTION "public"."get_max_min_value"("a" NUMERIC, "b" NUMERIC, "c" NUMERIC, OUT "min_value" NUMERIC, OUT "max_value" NUMERIC)
 AS `$BODY$`

BEGIN
    max_value := GREATEST(a,b,c);
    min_value := LEAST(a,b,c);
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

注意：函数的输出是一个记录，要使输出以列分隔，请使用以下语句

SELECT * FROM get_max_min_value(10,20,30);

INOUT：INOUT 参数是 IN 和 OUT 参数的组合。这意味着调用者可以将值传递给函数，函数然后改变参数并且将该值作为结果的一部分传递回去。
举例：

CREATE OR REPLACE FUNCTION "public"."get_square"(INOUT "a" NUMERIC)
 AS `$BODY$`

BEGIN
    a := a * a
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

返回一个数的平方

SELECT get_square(5);

声明变量

变量类型

TIMESTAMP、VARCHAR、INT4、NUMERIC、RECORD、CURSOR、表名.字段名 % TYPE 等等;
对RECORD、CURSOR、表名.字段名 % TYPE 做详细说明。
RECORD：记录数据类型，是将多个基本数据类型变量组合成一个整体，作为一个复合数据类型使用。需要注意的是，RECORD 记录数据类型，仅能接收单行多列的查询结果，如果查询结果返回是多行就需要配合数组使用。
CURSOR：游标，游标是一个存储在PgSQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集。
⑴声明游标：划分存储区域，注意此时并没有执行Select语句。
游标名 CURSOR (参数列表) FOR Select语句;
⑵打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。
OPEN 游标名(参数列表);
⑶获取记录：移动游标取一条记录
FETCH 游标名 INTO 临时记录或属性类型(例如RECORD类型)变量;
⑷关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。
CLOSE 游标名;
表名.字段名 % TYPE：直接使用数据表里面的字段的类型作为此变量的数据类型。

avatar
文章
阅读
粉丝