- 函数语法
- 实例
- 动态执行语句
话不多说，下面直接拿实例说话。 函数语法
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来表示。如果存在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):《春暮》
