PG自定义函数写法说明

     PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句。

1、自定义函数说明

语法：

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

function_name:指定函数的名称。

arguments: 函数参数

[OR REPLACE]:是可选的，它允许修改/替换现有函数。

DECLARE:定义参数(参数名写在前面 类型写在后面)。

BEGIN~END: 在中间写方法主体。

RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。

LANGUAGE:它指定实现该函数的语言的名称。 可以是SQL，PL/pgSQL，C， Python等。

2、函数的参数说明

   IN 参数

   你可以将 IN 参数传递给函数，但无法从返回结果里再获取到。

   OUT 参数

   OUT 参数经常用于一个函数需要返回多个值，所以不需要 RETURN 语句。

   INOUT 参数

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

   VARIADIC 参数

   PostgreSQL 函数可以接受可变数量的参数，其中一个条件是所有参数具有相同的数据类型。参数作为数组传递给函数。

参数使用例子：

CREATE OR REPLACE FUNCTION hi_lo(
 IN a NUMERIC, 
 IN b NUMERIC,
     OUT c NUMERIC, 
     OUT hi NUMERIC,
         INOUT lo NUMERIC)
AS $$
BEGIN
 c:= GREATEST(a,b);
 hi:= LEAST(a,b);
 lo:=GREATEST(a,b);
END; $$

LANGUAGE plpgsql;

3、其他相关

函数重载

      与 Java 等编程语言相同，PostgreSQL 允许多个函数具有相同的名称，只要参数不同即可。如果多个函数具有相同的名称，那么我们说这些函数是重载的。当一个函数被调用时，PostgreSQL 根据输入参数调用确切的函数。

块结构

一个 PostgreSQL 函数由块（block）进行组织。

[ DECLARE
    声明 ]
BEGIN
    主体;
 ...
END;

每个块有两个部分，称为声明和主体。声明部分是可选的，而主体部分是必需的。该块在END关键字后以分号（;）结尾。声明部分中的每个语句都以分号（;）结尾。 主体部分中的每个语句也以分号（;）结尾。

4、调用函数

① 定义好该函数后，我们可以像调用其他的函数一样调用它

SELECT test(id)

 test为自定义函数名称，返回函数定义的返回内容。

② Mybatis mapper中使用函数

<!--Mybatis中使用函数-->
    <insert id="testFun">
         DO $$
            DECLARE num INTEGER=0;
            BEGIN
             select count(*) into num as  num from (select distinct cardnum from         
             bus_data) as A;
             insert into ic_test
             select num, dt
             from bus_data limit 100;
            END  $$;
    </insert>

在mapper接口里面调用：

void testFun(String dtstr);

5、 函数示例，方便取用：

① 传入一个list，返回list的和、以及平均数

CREATE OR REPLACE FUNCTION sum_avg(
 VARIADIC list NUMERIC[],
 OUT total NUMERIC, 
        OUT average NUMERIC)
AS $$
BEGIN
   SELECT INTO total SUM(list[i])
   FROM generate_subscripts(list, 1) g(i);

   SELECT INTO average AVG(list[i])
   FROM generate_subscripts(list, 1) g(i);

END; $$
LANGUAGE plpgsql;

②计算管线是否在多边形范围内
参数：管线首尾坐标，多边形最小x，最大x，最小y，最大y

CREATE OR REPLACE  function fun_isinarea(x1 numeric,y1 numeric,x2 numeric,y2 numeric,xmin numeric,xmax numeric,ymin numeric,ymax numeric) 
returns BOOLEAN 
as 
$BODY$
declare bResult boolean=false;
begin
   if((x1>=xmin and x1<=xmax) and (y1>=ymin and y1<=ymax)) then bResult=true;
   end if;
   if((x2>=xmin and x2<=xmax) and (y2>=ymin and y2<=ymax)) then bResult=true;
   end if;
   return bResult ;
end;
$BODY$
LANGUAGE plpgsql VOLATILE