SQL存储过程/函数(POSTGRES)

POSTGRES存储过程/POSTGRES函数

关键词： SQL存储过程/函数，SQL存储过程，SQL函数， SQL存储过程数组

一、函数定义格式：

CREATE OR REPLACE FUNCTION func_name([输入参数]) RETURNS 返回值 AS 
$func$
[DECLARE
	变量声明;
]
BEGIN
	函数体;
END
$func$
LAGGUAGE plgpsql;

关键字说明（中文字体部分）
1.1 输入参数，变量声明

关键字说明	举例
输入参数 variabe_name variable_type，多个用逗号(,)分隔；	eg: func_name(_table varchar, _id int, _schema TEXT = ‘public’)
变量声明 variable_name variable_type := value 这一部分可有可无（方括号范围内），取决于函数体的需要，但是函数体中用到的变量都需要在此处声明。	eg: v_cnt int; v_table_name varchar; table_ids varchar[]; table_ids text[]; – 单行注释：data_ids 返回的是结果集（可多列，具体字段点取） data_ids CURSOR FOR query_sql;

1.2 函数体(这一部分涉及到程序的具体逻辑)
1.2.1 变量声明部分中的变量可以在此处赋值，赋值方式有:

说明	变量赋值
常规赋值	v_cnt := 0;
– SQL语句执行结果	SELECT query_sql INTO v_varibale;
数组赋值	table_ids := array[‘hr_company’, ‘hr_deparment’, ‘hr_employee’]
/** SQL语句和变量的拼接 %I 表示表结构中的表名，字段名, %s 表示值 填充变量个数是动态的 */	FROMAT(‘SELECT * FROM %I.%I = %s’， v_table_name, ‘id’, v_cnt );
EXCUTE 表示执行SQL语句, 如果SQL有返回结果（计数/字符串等）， 可以加上 INTO 表达式(分号需要放在一个完整表达式的末尾)；	EXCUTE 上式 [INTO v_variable];

1.2.2 程序流程控制（程序的判断，循环）
(1) 条件判断

	//(单分支)
	IF condition THEN 
		statement;
	END IF;
	//(多分支)
	IF condition1 THEN
		statement1;
	ELIF condition2 THEN
		statement2;
	ELSE
		statement3;
	END IF;

(2) 循环语句

	//(循环结果集)
	FOR rec IN result_cursor/SQL语句
	LOOP
		statement;
	END LOOP;
	//(循环数组)
	FOREACH rec IN array result_array
	LOOP
		statement;
	END LOOP;

1.2.3 函数返回值
按照SQL函数语法规范，必须有定义RETURNS 语句
  (1) 函数体返回常规类型（varchar, text, int, float 等）;
  (2) 函数体无返回值（RETURNS VOID）;
  (3) 函数返回结果集

	//1. (通过输出参数返回)
	/*
		1). 函数的参数(无关键字默认为输入参数)，通过IN/OUT区分输入/输出参数(两者都有的时候)；
		2). 通过输出参数返回，结果集有多少列，就有多少个输出参数
	*/
	
	//函数体格式
	CREATE OR REPLACE function get_result1(
		IN table_name TEXT,
		OUT column1 int,
		OUT column2 varchar)
	RETURNS SETOF RECORD AS --返回结果为SETOF 集合类型
	-- RECROD 为postgres内置集合类型，也可以在变量声明时使用
	$func$
	DECLARE
		rec RECORD;
	BEGIN
		-- 此处的SQL可以做某些操作
		FOR rec IN EXECUTE FORMAT('SELECT id, name FROM %I', table_name)
		LOOP
			column1 = rec.id;
			column2 = rec.name;
			RETURN NEXT;
		END LOOP;
	END
	$func$
	LANGUAGE plpgsql;
	
	// 2. 通过自定义结果集返回
	DROP FUNCTION IF EXISTS get_result2;
	DROP TYPE IF EXISTS personal_rec;
	CREATE TYPE personal_rec AS (id int, name varchar);
	CREATE OR REPLACE function get_result2(
		IN table_name TEXT)
	RETURNS SETOF personal_rec AS --返回自定义结果集类型
	$func$
	DECLARE
		rec personal_rec ;
	BEGIN
		-- 此处的SQL可以做某些操作
		FOR rec IN EXECUTE FROMAT('SELECT id, name FROM %I', table_name)
		LOOP
			RETURN NEXT rec;
		END LOOP;
	END
	$func$
	LANGUAGE plpgsql;

二、一个实例（数据表备份）
这个实例是对即将要批量修改的数据表进行备份操作，复制原表的结构与数据，存到以时间为后缀的新表中。

CREATE OR REPLACE FUNCTION copy_material_table() RETURNS VOID AS
$func$
DECLARE
	table_ids varchar[];
	table_ids_regex text[];
	table_id varchar;
	new_table_id varchar;
	v_ts varchar;
	v_table_regex varchar;
	v_exist int;
BEGIN
	-- 要批量修改的表明，此处需修改（或者放在输入参数中处理）
	table_ids := array['table1', 'table2', 'table3'];
	SELECT to_char(now(), 'YYYYMMDDHH') INTO v_ts;
	/*预处理-构建表名正则*/
	FOREACH table_id in array table_ids
	LOOP
		SELECT ARRAY_APPEND(table_ids_regex, table_id||'\_\d{10}$'::text) INTO table_ids_regex;
	END LOOP;
	SELECT ARRAY_TO_STRING(table_ids_regex, '|') INTO v_table_regex;
	
	/*已经执行过则直接退出*/
	EXECUTE FORMAT(
		'SELECT count(1) FROM pg_tables WHERE tablename ~ ''%s''',
		v_table_regex
	) INTO v_exist;
	IF v_exist > 0 THEN
		raise notice '已经创建(%)', v_exist;
		return;
	END IF;
	
	/*复制表结构合数据*/
	FOREACH table_id in array table_ids
	LOOP
		new_table_id := table_id || '_' || v_ts;
		/*复制表结构*/
		EXECUTE FORMAT(
			'DROP TABLE IF EXISTS %I;
			 CREATE TABLE %I(like %I including all);
			',
			new_table_id, new_table_id, table_id
		);
		/*复制表数据*/
		EXECUTE FORMAT(
			'INSERT INTO %I SELECT * FROM %I;',
			new_table_id, table_id
		);
		raise notice '%表备份复制完成.', table_id;
	END LOOP;
END
$func$
LANGUAGE plpgsql;


/*执行备份函数*/
SELECT "copy_material_table"();