PostgreSQL自定义函数

PostgreSQL自定义函数函数语法实例动态执行语句话不多说，下面直接拿实例说话。函数语法CREATE [OR REPLACE] FUNCTION function_name (arguments)RETURNS return_datatype AS $variable_name$DECLAREdeclaration;[...]BEGIN< function_body >[...]R

秋漓

10388人浏览 · 2022-04-27 14:24:10

秋漓 · 2022-04-27 14:24:10 发布

PostgreSQL自定义函数

函数语法
实例
动态执行语句

话不多说，下面直接拿实例说话。

函数语法

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

说明：
function_name：指定函数的名称。
[OR REPLACE]：是可选的，它允许您修改/替换现有函数。
arguments:函数参数[[argmode]argname argtype [default value],[[argmode]argname argtype],[…]];argmode参数的模式有IN、OUT或则INOUT，缺省值是IN。argname参数名字。argtype参数的数据类型。default默认参数。
RETURNS：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型，比如return int、varchar，返回结果集时就需要setof来表示，无返回值使用void。如果存在OUT或则INOUT参数，则可以省略RETURNS子句。
return_datatype：表示返回值类型。
function_body：function_body包含可执行部分。
LANGUAGE：它指定实现该函数的语言的名称。

在PostgreSQL中可以利用RAISE语句报告信息和抛出错误，其声明形式为：

RAISE LEVEL 'format' [,expression,[...]]

说明：
LEVEL：包含的级别有DEBUG(向服务器日志写信息)、LOG(向服务器日志写信息，优先级更高)、INFO、NOTICE和WARNING(把信息写到服务器日志以及转发到客户端应用，优先级逐步升高)和EXCEPTION抛出一个错误(通常退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志，还是两个均有，是由log_min_messages和client_min_messages这两个系统初始化参数控制的。

用于根据格式字符串设置参数的FORMAT()函数格式：

FORMAT(format_string [, format_arg [, ...] ])

实例

1、format函数

SELECT FORMAT('Hello, %s', 'Geeks!!');

执行结果：Hello, Geeks!!

2、使用default参数

create or replace function ftest(in fname VARCHAR default '水果', cname VARCHAR DEFAULT '西瓜')
RETURNS VARCHAR as $$
begin
	 return format('%s_%s',fname,cname);
end; $$ LANGUAGE plpgsql;

调用方式：

select ftest('食物');  --食物_西瓜
select ftest(cname=>'苹果'); --水果_苹果

3、根据输入的数字，返回两个数的商，若除数为0，则抛出自定义异常

CREATE 
	OR REPLACE FUNCTION testdivision ( js1 INT, js2 INT ) RETURNS INT AS $$ DECLARE
	v_re INT;
BEGIN
		raise notice'% 除以 %',
		js1,
		js2;
	IF
		js2 = 0 THEN
			raise EXCEPTION '不能除0';
		ELSE v_re := js1 / js2;
		RETURN v_re;
		
	END IF;
	EXCEPTION --捕获异常
	
	WHEN OTHERS THEN
		RETURN 0;
	
END;
$$ LANGUAGE plpgsql;

调用方式：

select testdivision(6,3);--2
select testdivision(6,0);--0

4、多个OUT或者INOUT参数时，返回是一个元组；可以通过将函数调用放在 FROM 子句中来返回元组的各个成员

create or replace FUNCTION test1(in js1 int,inout res1 int,out res2 int) as
$$
begin
   res1:=js1*2;
	 res2:=res1*3;
end;
$$
LANGUAGE plpgsql;

调用方式：

select test1(3,2); --test1
                   --(6,18)
									 
select * from test1(3,2);--res1|res2
                         --   6|  18

动态执行语句

数据准备：

create table tmp(
 bsm VARCHAR(100),
 name VARCHAR(100),
 num int
);
insert into tmp(bsm,name,num) VALUES('a','苹果',21);
insert into tmp(bsm,name,num) VALUES('b','香蕉',11);

实例
1、动态查询某张表的记录数

create or replace function getsum(in talename VARCHAR)
RETURNS int as $$
DECLARE
   stmt VARCHAR;
	 count int;
begin
  stmt:=format('select count(1) from %s', talename);
  raise notice '%',stmt;
	EXECUTE stmt into count;
	return count;
	
	EXCEPTION --捕获异常
	
	WHEN OTHERS THEN
		RETURN 0;
end; $$ LANGUAGE plpgsql;

调用方式：select getsum('tmp');--2

2、动态创建表

create or replace function copytable(tablename varchar,times int)
RETURNS INT as $$
DECLARE
   stmt VARCHAR='create table %s_%s (like tmp including all);';--
begin
   for i in 1..times loop
	     raise notice 'd当前次数%',i;
			 raise notice '%',format(stmt,tablename,i,tablename);
			 EXECUTE format('drop table if EXISTS %s_%s;',tablename,i) ;
			 EXECUTE format(stmt,tablename,i,tablename) ;
	 end loop;
	 return 0;			
END; $$  LANGUAGE plpgsql;