PostgreSQL自定义函数_秋漓_postgresql 自定义函数

irpas 02-07 6787

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;

调用方式：select copytable('tmp',2);即复制tmp表2次

门外无人问落花，绿阴冉冉遍天涯。 --曹豳(bīn):《春暮》