PostgreSQL自定义函数并且调用方式

一、背景

在PostgreSQL关系型数据库中，我们经常是调用系统默认的函数，例如lower() ,arry_to_string()等等，但有时候特殊的需求，默认的函数无法实现转换，那么就需要通过自定义函数，并且调用我们自定义的函数实现数据的转换。

二、函数语法

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这两个系统初始化参数控制的。

四、自定义函数示例

1、format函数

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

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

示例：

SELECT FORMAT(\’Hello, %s\’, \’world!!\’);

执行结果：Hello, world!!

2、使用default参数

create or replace function ftest(in fname VARCHAR default \’编程语言\’, cname VARCHAR DEFAULT \’java\’)
RETURNS VARCHAR as $$
begin
return format(\’%s_%s\’,fname,cname);
end; $$ LANGUAGE plpgsql;

调用方式：

select ftest(\’语言\’); –语言_java
select ftest(cname=>\’java\’); –编程语言_java

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(8,4);–2
select testdivision(3,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

五、动态执行语句

1、数据准备

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

2、示例：动态查询某张表的记录数

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

3、动态创建表

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;

调用方式：

select copytable(\’tmp\’,2);即复制tmp表2次

到此这篇关于PostgreSQL如何自定义函数并且调用的文章就介绍到这了,更多相关PostgreSQL自定义函数内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源！