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"();