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

前言：

数据库中的函数概念

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

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

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

一，

查询任意表的记录数

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 1111;
end; $$ LANGUAGE plpgsql;

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

调用函数：

select getsum('emp');

结果如下：

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

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

test=# select getsum('erwe');
NOTICE:  select count(1) from erwe
 getsum 
--------
   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 THEN
		RETURN 1111;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

调用该函数：

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

二，

利用函数优雅的创建用户

CREATE OR REPLACE FUNCTION "public"."__tmp_create_user"()
  RETURNS "pg_catalog"."void" AS $BODY$
BEGIN
  IF NOT EXISTS (
          SELECT                       -- SELECT list can stay empty for this
          FROM   pg_catalog.pg_user
          WHERE  usename = 'postgres_exporter') THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

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

三，

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

create or replace function copytable(tablename varchar,times int)
RETURNS INT as $$
DECLARE
   stmt VARCHAR='create table %s_%s as select * from emp;';--
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 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 VOLATILE
  COST 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$
DECLARE
   stmt1 varchar(1000);
	 stmt2 varchar(1000);
begin
   for i in 1..times loop
	     raise 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 VOLATILE
  COST 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)