函数

PostgreSQL 编程
146
2023/08/02 09:46:35
postgresql

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

创建函数

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);

PosgreSQL还支持使用命名参数的方式调用函数：

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

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

函数参数的模式

INOUTINOUT
默认模式需要明确指定需要明确指定
将值传递给函数从函数中返回一个值将值传递给函数并返回更新后的值
IN参数的作用类似于常量OUT 参数的作用类似于未初始化的变量INOUT 参数的作用类似于初始化变量
无法赋值必须赋值应该赋值

以下示例演示了 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 $$;

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

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

create or replace function get_film_stat(
    out min_len int,
    out max_len int,
    out avg_len numeric) 
language plpgsql
as $$
begin
  
  select min(length),
         max(length),
		 avg(length)::numeric(5,1)
  into min_len, max_len, avg_len
  from film;

end $$;

调用该函数：

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 ) 

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();