postgresql|自定义函数的设计和实现

前言：

数据库中的函数概念

和开发语言，Java，PHP，Python等等类似，关系型数据库也是有函数的，函数指的是动态的封装一部分特定功能的集合。

例如，查询任意表的记录数，复制指定表，这些函数可以简化一些数据库的操作

那么，最为简单直观的方法还是用一些演示示例来说明函数是什么以及函数的特性。

一，

查询任意表的记录数

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

可以看到，该函数是一个带参函数，参数就是要查询的表的名称 

调用函数：

select getsum('emp');

结果如下：

test=# select getsum('emp');
NOTICE:  select count(1) from empgetsum 
--------14
(1 row)

如果查询的是不存在的表，那么，将会返回函数定义的1111：

test=# select getsum('erwe');
NOTICE:  select count(1) from erwegetsum 
--------1111
(1 row)

查询指定表的记录数：

CREATE OR REPLACE FUNCTION "public"."totalrecords"()RETURNS "pg_catalog"."int4" AS $BODY$  
declare  total integer;  
BEGIN  SELECT count(*) into total FROM emp;  RETURN total;  EXCEPTION --捕获异常WHEN OTHERS THENRETURN 1111;
END;  
$BODY$LANGUAGE plpgsql VOLATILECOST 100

调用该函数：

test=# select totalrecords();totalrecords 
--------------14
(1 row)

二，

利用函数优雅的创建用户

CREATE OR REPLACE FUNCTION "public"."__tmp_create_user"()RETURNS "pg_catalog"."void" AS $BODY$
BEGINIF NOT EXISTS (SELECT                       -- SELECT list can stay empty for thisFROM   pg_catalog.pg_userWHERE  usename = 'postgres_exporter') THENCREATE USER postgres_exporter;END IF;
END;
$BODY$LANGUAGE plpgsql VOLATILECOST 100

该函数有if逻辑，其它没有什么太特殊的地方。

三，

复制指定表emp的数据不包括索引，约束，外键，指定份数：

create or replace function copytable(tablename varchar,times int)
RETURNS INT as $$
DECLAREstmt VARCHAR='create table %s_%s as select * from emp;';--
beginfor i in 1..times loopraise 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 100	
END; $$  LANGUAGE plpgsql;

该函数是for循环参数，循环创建指定名称的复制表，源表的名称是emp，emp表是真实存在的

四，

函数做加法

CREATE OR REPLACE FUNCTION "public"."add"("a" int4, "b" numeric)RETURNS "pg_catalog"."numeric" AS $BODY$SELECT a+b;
$BODY$LANGUAGE sql VOLATILECOST 100
select add(3,4);

调用函数：

test=# select add(55,6);add 
-----61
(1 row)

五，

函数复制表--同时复制索引，主键，约束，tablename1是新表名称加复制次数，tablename2是要复制的源表名称

CREATE OR REPLACE FUNCTION "public"."copytable"("tablename1" varchar, "tablename2" varchar, "times" int4)RETURNS "pg_catalog"."int4" AS $BODY$
DECLAREstmt1 varchar(1000);stmt2 varchar(1000);
beginfor i in 1..times loopraise notice '当前次数%',i;stmt1 := format('create table "%1$s_%3$s" (like "%2$s" INCLUDING INDEXES INCLUDING DEFAULTS);',tablename1,tablename2,i);stmt2 := format('insert into "%1$s_%3$s" select * from "%2$s" ;',tablename1,tablename2,i);raise notice '%',stmt1;raise notice '%',stmt2;EXECUTE format('drop table if EXISTS "%1$s_%3$s";',tablename1,tablename2,i) ;EXECUTE stmt1 ;EXECUTE stmt2 ;end loop;return 110;			
END; $BODY$LANGUAGE plpgsql VOLATILECOST 100

该函数的调用示例（emp是源表，test表是新表，此函数将会建立两个表，表名分别是test_1和test_2)

另外，需要注意的是，该函数是冥等函数，也就是说，可以反复执行任意次，结果都是不变的。

test=# select copytable('test','emp',2);
NOTICE:  当前次数1
NOTICE:  create table "test_1" (like "emp" INCLUDING INDEXES INCLUDING DEFAULTS);
NOTICE:  insert into "test_1" select * from "emp" ;
NOTICE:  当前次数2
NOTICE:  create table "test_2" (like "emp" INCLUDING INDEXES INCLUDING DEFAULTS);
NOTICE:  insert into "test_2" select * from "emp" ;copytable 
-----------110
(1 row)