创建 PostgreSQL 函数案例

一、前言

最近因为项目需要创建了数据库函数，数据库是 PostgreSQL ，这里做个创建函数的记录。

二、准备数据

准备了如下数据：

后续创建函数都使用这些数据。

三、案例

1、简单使用

假如要将如下 sql 放到函数里：

select * from mr m order by m.create_time desc;

创建如下函数：

create or replace function get_mr_data_1()
returns setof mr as
$BODY$
		begin
				return query select * from mr m order by m.create_time desc;
		end;
$BODY$
language plpgsql;

使用函数查询， sql 如下：

select get_mr_data_1();

解释：

create or replace function get_mr_data_1()  -- 创建名为get_mr_data_1()的函数，存在时替换。
returns setof mr as   -- 返回类型，这里为mr（我的表）
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		begin  -- 函数的开始标志
				return query select * from mr m order by m.create_time desc;
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。

2、返回单个字段

假如只返回 name 这个字段， sql 如下：

select m.name from mr m order by m.create_time desc;

创建如下函数：

create or replace function get_mr_data_2()  
returns table(name varchar) as   
$BODY$     
		begin  
				return query select m.name from mr m order by m.create_time desc;
		end;   
$BODY$     
language plpgsql;  

使用函数查询

select get_mr_data_2();

解释：

create or replace function get_mr_data_2()  -- 创建名为get_mr_data_2()的函数，存在时替换。
returns table(name varchar) as   -- 返回类型，这里为返回字段 name ，返回类型为 varchar 
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		begin  -- 函数的开始标志
				return query select m.name from mr m order by m.create_time desc;
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。

3、返回多个字段

假如要返回 id ，name ，create_time 这个三个字段

select m.id, m.name, m.create_time from mr m order by m.create_time desc;

创建如下函数：

create or replace function get_mr_data_3()  
returns table(id varchar, name varchar, create_time timestamp) as   
$BODY$     
		begin  
				return query select m.id, m.name, m.create_time from mr m order by m.create_time desc;
		end;   
$BODY$     
language plpgsql; 

使用函数查询：

select get_mr_data_3();

解释：

create or replace function get_mr_data_3()  -- 创建名为get_mr_data_2()的函数，存在时替换。
returns table(id varchar, name varchar, create_time timestamp) as   -- 返回类型，这里为返回字段分别为 id ，name ，create_time ，返回类型分别为 varchar ，varchar ，timestamp
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		begin  -- 函数的开始标志
				return query select m.id, m.name, m.create_time from mr m order by m.create_time desc;
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。

4、传入参数

查询 id 为 1843560690671853578 的记录

select m.id, m.name, m.create_time from mr m where m.id = '1843560690671853578'

创建如下函数：

create or replace function get_mr_data_4(select_id varchar)  
returns table(id varchar, name varchar, create_time timestamp) as   
$BODY$     
		begin  
				return query select m.id, m.name, m.create_time from mr m where m.id = select_id;
		end;   
$BODY$     
language plpgsql;

使用函数查询

select get_mr_data_4('1843560690671853578')

解释：

create or replace function get_mr_data_4(select_id varchar)  -- 创建名为get_mr_data_4()的函数，存在时替换，参数名为 select_id ，类型为 varchar ，查询参数类型和数据库字段类型保持一致。
returns table(id varchar, name varchar, create_time timestamp) as   -- 返回类型，这里为返回字段分别为 id ，name ，create_time ，返回类型分别为 varchar ，varchar ，timestamp
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		begin  -- 函数的开始标志
				return query select m.id, m.name, m.create_time from mr m where m.id = select_id;
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。

5、在函数中产生变量返回

模糊查询 name 并返回结果数据条数，sql 如下：

select count(*) from mr m where m.name ilike CONCAT('%', 'upload', '%');

创建如下函数：

create or replace function get_mr_data_5(select_name varchar)  
returns int as   
$BODY$     
		declare 
				num int;
		begin  
				select count(*) into num from mr m where m.name ilike CONCAT('%', select_name, '%');
				return num; 
		end;   
$BODY$     
language plpgsql;

使用函数查询：

select get_mr_data_5('upload')

解释：

create or replace function get_mr_data_5(select_name varchar)  -- 创建名为get_mr_data_5()的函数，存在时替换，参数名为 select_name ，类型为 varchar ，查询参数类型和数据库字段类型保持一致。
returns int as   -- 返回类型，这里为 int ，和返回类型保持一致
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		declare   -- 定义变量
				num int;  -- 变量为 num ，类型为 int ，假如有多个变量，每个变量之后带上“:”即可
		begin  -- 函数的开始标志
				select count(*) into num from mr m where m.name ilike CONCAT('%', select_name, '%');  -- into 将查询结果赋值给变量 num 
				return num;  -- 返回变量 num
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。

6、实现分页

使用字段 name 进行模糊查询，查询第一页，没用十条数据， sql 如下：

select m.id, m.name, m.create_time from mr m where m.name ilike CONCAT('%', 'up', '%') order by m.create_time desc offset 0 limit 10;

创建如下函数：

create or replace function get_mr_data_6(select_name varchar, page_num integer, page_size integer) 
returns table(id varchar, name varchar, create_time timestamp) as
$BODY$
		begin
				return query select m.id, m.name, m.create_time from mr m where m.name ilike CONCAT('%', select_name, '%') order by m.create_time desc offset (page_num - 1) * page_size limit page_size;
		end;
$BODY$
language plpgsql;

使用函数查询：

select get_mr_data_6('up', 1, 10)

可以看到实现了分页。

解释：

create or replace function get_mr_data_6(select_name varchar, page_num integer, page_size integer)  -- 创建名为get_mr_data_6()的函数，存在时替换，参数名分别为 select_name 、page_num 、page_size ，类型为 varchar 、 integer 、integer ，查询参数类型和数据库字段类型保持一致。
returns int as   -- 返回类型，这里为 int ，和返回类型保持一致
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
		begin  -- 函数的开始标志
				return query select m.id, m.name, m.create_time from mr m where m.name ilike CONCAT('%', select_name, '%') order by m.create_time desc offset (page_num - 1) * page_size limit page_size;  -- postgresql 的 offset 1 limit 10 相当于 mysql 的 limit 1 10
		end;   -- 函数的结束标志
$BODY$     -- 在 $BODY$ 之间编写函数体，也可以使用 $$
language plpgsql;   -- 指定 language plpgsql 来告诉 PostgreSQL 使用 PL/pgSQL 来编译和执行函数。