函数

本章我们将讨论自定义函数的相关知识，包括：创建函数、函数参数的模式、函数重载、返回表的函数以及删除函数等知识。

创建函数

CREATE FUNCTION 用于创建自定义函数，语法如下：

create [or replace] function function_name(param_list)
   returns return_type 
   language plpgsql
  as
$$
declare 
-- variable declaration
begin
 -- logic
end
$$;

以下示例创建了一个函数，用于对片长的 len_from和len_to 参数之间的影片进行计数：

create function get_film_count(len_from int, len_to int)
returns int
language plpgsql
as
$$
declare
   film_count integer;
begin
   select count(*) 
   into film_count
   from film
   where length between len_from and len_to;
   
   return film_count;
end
$$;

之后便可以调用该函数了：

select get_film_count(40,90);

select get_film_count(
    len_to => 90,
    len_from => 40
);

这种方式下，参数的位置就无关紧要了。

函数参数的模式

PL/pgSQL 支持三种参数模式：

以下示例演示了 IN 模式。通过 ID 查找电影，并返回其标题：

create or replace function find_film_by_id(p_film_id int)
returns varchar
language plpgsql
as $$
declare
   film_title film.title%type;
begin
  select title 
  into film_title
  from film
  where film_id = p_film_id;
  
  if not found then
     raise '没有找到ID是%的电影', p_film_id;
  end if;
  
  return title;
  
end $$;

由于没有指定参数 p_film_id 的模式，所以默认采用 IN 模式。

OUT 模式的参数作为结果的的一部分返回，它在需要返回多个值的函数中非常有用。需要显式地在参数名前面加上 OUT 关键字进行定义。

以下示例定义了具有三个 OUT 参数的 get_film_stat 函数：

调用该函数：

select * from get_film_stat();

/* 结果
 min_len | max_len | avg_len
---------+---------+---------
      46 |     185 |   115.3
*/

INOUT模式是IN和OUT模式的结合，可以将参数传递给函数，函数更改参数并将更新后的值返回。

以下 swap 函数接受两个 INOUT 参数，并将它们的值进行交换：

create or replace function swap(
	inout x int,
	inout y int
) 
language plpgsql	
as $$
begin
   select x,y into y,x;
end $$;

select * from swap(10,20);

/* 结果
 x  | y
----+----
 20 | 10
*/

函数重载

PL/pgSQL 支持函数重载：函数名相同，但函数签名不同。

以下示例返回指定客户的总租赁天数：

create or replace function get_rental_duration(
	p_customer_id integer
)
returns integer 
language plpgsql
as $$
declare 
	rental_duration integer; 
begin
	select 
		sum( extract(day from return_date - rental_date)) 
	into rental_duration 
    from rental 
	where customer_id = p_customer_id;

	return rental_duration;
end $$;

调用该函数：

-- 返回客户ID为232的总租赁天数
SELECT get_rental_duration(232);

假设需要了解客户从特定日期到现在的租赁天数，可以通过定义一个两个参数的get_rental_duration函数来构成重载：

create or replace function get_rental_duration(
	p_customer_id integer, 
	p_from_date date
)
returns integer 
language plpgsql
as $$
declare 
	rental_duration integer;
begin
	select sum( extract( day from return_date + '12:00:00' - rental_date)) 
	into rental_duration
	from rental 
	where customer_id = p_customer_id and 
		  rental_date >= p_from_date;
	
	return rental_duration;
end $$;

调用函数：

-- 返回ID为232的客户，自2005年7月1日以来的总租赁天数
SELECT get_rental_duration(232,'2005-07-01');

返回表的函数

使用以下语法可以创建返回表的函数：

create or replace function function_name (
   parameter_list
) 
returns table ( column_list ) 
language plpgsql
as $$
declare 
-- 声明变量
begin
-- 函数体
end $$; 

此语法允许返回表，而不是单个值：

returns table ( column_list ) 

以下函数使用 ILIKE 运算符返回标题中包含某字符的电影：

create or replace function get_film (
  p_pattern varchar
) 
	returns table (
		film_title varchar,
		film_release_year int
	) 
	language plpgsql
as $$
begin
	return query 
		select
			title,
			release_year::integer
		from
			film
		where
			title ilike p_pattern;
end $$;

调用该函数：

SELECT * FROM get_film ('Al%');

在实践中，通常会先处理每一行，然后再进行返回：

create or replace function get_film (
	p_pattern varchar,
	p_year int
) 
returns table (
	film_title varchar,
	film_release_year int
) 
language plpgsql
as $$
declare 
    var_r record;
begin
	for var_r in(
            select title, release_year 
            from film 
	     where title ilike p_pattern and 
		    release_year = p_year
        ) loop  film_title := upper(var_r.title) ; 
		film_release_year := var_r.release_year;
           return next;
	end loop;
end $$;

调用该函数：

SELECT * FROM get_film ('%er', 2006);

删除函数

使用 DROP FUNCTION 可以删除指定函数，语法如下：

drop function [if exists] function_name[(argument_list)]
[cascade | restrict]

• 尝试删除不存在的函数时，数据库会报错，加上 if exists 可以避免这个错误
• function_name：要删除的函数名
• argument_list：参数列表
  • 如果函数实现了重载，需要通过参数列表来显式指定要删除哪个重载版本
  • 如果没有实现重载，参数列表可以不用指定
• cascade：当有其它的对象依赖于该函数时，将在删除该函数的同时，级联删除所有依赖它的其它对象
• restrict：当有其它对象依赖于该函数时，禁止删除该函数。这是默认行为。

• 如果函数实现了重载，需要通过参数列表来显式指定要删除哪个重载版本
• 如果没有实现重载，参数列表可以不用指定

示例：

-- 删除swap函数，它没有重载版本，所以可以直接使用函数名
DROP FUNCTION swap;
-- 或者
DROP FUNCTION swap();

-- 删除get_film函数，它有两个重载版本，所以需要通过参数列表来指定
DROP FUNCTION get_film(varchar);
DROP FUNCTION get_film(varchar, int);

-- 重载版本的函数，一旦只剩下一下版本了，也可以像没有重载版本的函数那样直接使用函数名进行删除
DROP FUNCTION get_film;
-- 或者
DROP FUNCTION get_film();