pgsql 的函数

pgsql 的函数

因为pgsql中没有存储过程和包，所以类似功能通过函数来实现

PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458 
存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html 
PL/pgSQL - SQL存储过程语言 https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%B8%89%E5%8D%81%E4%B9%9D%E7%AB%A0

postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html 
结构 
PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。

基本上是这样的：

1. CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, ...])
2. RETURNS 返回值类型 AS
3. $BODY$
4. DECLARE
5. 变量声明
6. BEGIN
7. 函数体
8. END;
9. $BODY$
10. LANGUAGE ‘plpgsql’ VOLATILE;

变量类型 
除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

 
赋值 
赋值和Pascal有点像：“变量 := 表达式;” 
有些奇怪的是连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’; 
判断 
判断又和VB有些像： 
IF 条件 THEN 
… 
ELSEIF 条件 THEN 
… 
ELSE 
… 
END IF; 
循环 
循环有好几种写法： 
WHILE expression LOOP 
statements 
END LOOP; 
还有常用的一种是：（从1循环到9可以写成FOR i IN 1..9 LOOP） 
FOR name IN [ REVERSE ] expression .. expression LOOP 
statements 
END LOOP; 
其他 
还有几个常用的函数： 
SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型） 
PERFORM query; 表示执行query并丢弃结果 
EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）

参数: 
传递给函数的参数都是用 $1，$2，等等这样的标识符。

有时候为了增强可读性，我们可以为 $n 参数名声明别名。

然后通过这个别名或者数字标识符可以指向这个参数值。

 
有两种方法创建一个别名。

最好的方法是用CREATE FUNCTION命令给予这个参数一个名字，例如：

1. CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
2. BEGIN
3. RETURN subtotal * 0.06;
4. END;
5. $$ LANGUAGE plpgsql;

另一个方法是，在PostgreSQL 8.0之前唯一的方法，明确的用别名进行声明，用以下的语法进行声明： 
   name ALIAS FOR $n; 
这个风格的同一个例子看起来像下面这样 ：

1. CREATE FUNCTION sales_tax(real) RETURNS real AS $$
2. DECLARE
3. subtotal ALIAS FOR ;
4. BEGIN
5. RETURN subtotal * 0.06;
6. END;
7. $$ LANGUAGE plpgsql;

注意：这两个例子不是完全一样的。

在第一种情况，subtotal可以用sales_tax.subtotal进行引用，但是在第二种情况下不能这么做。

（如果我们给这个内部块附加了一个标签，subtotal能够替代这个标签） 
一些更多的例子：

1. CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
2. DECLARE
3. v_string ALIAS FOR ;
4. index ALIAS FOR ;
5. BEGIN
6. -- some computations using v_string and index here
7. END;
8. $$ LANGUAGE plpgsql;
9. CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
10. BEGIN
11. RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
12. END;
13. $$ LANGUAGE plpgsql

当一个PL/pgSQL函数用输出参数来进行声明时，给予这个输出参数$n名和一个任意的别名跟正常输入参数是同样的方法。

即使这个输出参数以NULL开始时也是一个有效的变量，它应该在函数的执行过程中被分配。

这个参数最好的值将被返回。

例如，这个sales-tax例子也可以用这种方法完成：

1. CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
2. BEGIN
3. tax := subtotal * 0.06;
4. END;
5. $$ LANGUAGE plpgsql;

注意：我们省略了RETURNS real---我们可以将它包括在内，但它是多余的。

 
当返回多个值的时候输出参数将非常有用，一个简单的例子是：

1. CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
2. BEGIN
3. sum := x + y;
4. prod := x * y;
5. END;
6. $$ LANGUAGE plpgsql;

如在Section 35.4.4中的讨论，这将为这个函数的结果创建一个匿名的记录类型。

如果使用了RETURNS字句，那么必须给它指明RETURNS记录。

 
另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如：

1. CREATE FUNCTION extended_sales(p_itemno int)
2. RETURNS TABLE(quantity int, total numeric) AS $$
3. BEGIN
4. RETURN QUERY SELECT quantity, quantity * price FROM sales
5. WHERE itemno = p_itemno;
6. END;
7. $$ LANGUAGE plpgsql;

这跟声明一个或者多个OUT参数和制定RETURNS SETOF这些类型是同样的方法。

 
当返回的PL/pgSQL函数的类型被声明为一个多态类型（anyelement, anyarray, anynonarray, 或者anyenum），特殊参数$0将被创建。

它的数据类型将实际的返回函数的类型，从实际的输入类型返回（见Section 35.2.5）。

这运行这个函数访问这个实际的返回类型如Section 39.3.3显示的那样。

$0初始值为空并且能够被函数修改，如果需要，它可以用于保留返回值，虽然这不是必须的。

$0也可以被给予一个别名。

例如，这个函数能在任意一个有+ *** 作符的数据类型上工作：

1. CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
2. RETURNS anyelement AS $$
3. DECLARE
4. result ALIAS FOR 
5. BEGIN
;
6. result := v1 + v2 + v3;
7. RETURN result;
8. END;
9. $$ LANGUAGE plpgsql
10. CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,

声明一个或者多个多态类型的输出参数也是同样的效果。

这种情况下这个特殊的$0参数将不会被用到，这个输出参数本身也是同样的作用，例如：

1. OUT sum anyelement)
2. AS $$
3. BEGIN
4. sum := v1 + v2 + v3;
5. END;
6. $$ LANGUAGE plpgsql;
39.3.2. 别名

DECLARE

 
   newname ALIAS FOR oldname; 
这个ALIAS语法比以前的章节中介绍的更加普通：你可以为任意一个变量声明一个别名，不只是函数的参数。

这实际的用途是用预定义的名字为变量定义不同的名字，如触发器过程中的NEW或者OLD。

例子：

1. prior ALIAS FOR old;
2. updated ALIAS FOR new;
3. CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8)

因此，ALIAS使同样的对象有两种不同的方式命名，如果不限制的使用，将会变得混乱。

这种方法最好只用于覆盖预定义的名字。

最后，贴出解决上面这个问题的存储过程吧：

1. RETURNS int4 AS
2. $BODY$
3. DECLARE
4. r RECORD;
5. del bool;
6. num int4 := 0;
7. sql "varchar";
8. BEGIN
9. sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';
10. FOR r IN EXECUTE sql LOOP
11. del := false;
12. IF r.receiveuserid=userid and r.senduserid=userid THEN
13. del := true;
14. ELSEIF r.receiveuserid=userid THEN
15. IF r.senddelete=false THEN
16. update message set receivedelete=true where id = r.id;
17. ELSE
18. del := true;
19. END IF;
20. ELSEIF r.senduserid=userid THEN
21. IF r.receivedelete=false THEN
22. update message set senddelete=true where id = r.id;
23. ELSE
24. del := true;
25. END IF;
26. END IF;
27. IF del THEN
28. delete from message where id = r.id;
29. num := num + 1;
30. END IF;
31. END LOOP;
32. return num;
33. END;
34. $BODY$
35. LANGUAGE 'plpgsql' VOLATILE;
36. CREATE OR REPLACE FUNCTION create_table_for_client(id int)

下面的例子是要调用一个存储过程自动创建对应的一系列表:

1. RETURNS integer AS
2. $BODY$
3. DECLARE
4. num int4 := 0;
5. sql "varchar";
6. BEGIN
7. sql := 'create table _' || id || '_company(id int, name text)';
8. EXECUTE sql;
9. sql := 'create table _' || id || '_employee(id int, name text)';EXECUTE sql;
10. sql := 'create table _' || id || '_sale_bill(id int, name text)';EXECUTE sql;
11. .......
12. return num;
13. END;
14. $BODY$ LANGUAGE plpgsql VOLATILE
15. CREATE OR REPLACE FUNCTION auto_gen_seq() RETURNS bigint AS

自动创建序列 
第一个例子

1. $BODY$
2. DECLARE
3. rd RECORD;
4. num int4 := 0;
5. sql "varchar";
6. seq_sql varchar;
7. BEGIN
8. sql := 'SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ''pg%'' AND tablename NOT LIKE ''sql_%'' ORDER BY tablename;';
9. FOR rd IN EXECUTE sql LOOP
10. seq_sql:='CREATE SEQUENCE SQ_'||rd.tablename||' START 1000000 CACHE 30;';
11. BEGIN
12. EXECUTE seq_sql;
13. EXCEPTION
14. WHEN TOO_MANY_ROWS THEN
15. RAISE EXCEPTION 'employee % not unique', seq_sql;
16. WHEN OTHERS THEN
17. return -1;
18. END;
19. num := num + 1;
20. END LOOP;
21. return num;
22. END;
23. $BODY$
24. LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
25. COST 100;
26. select auto_gen_seq()

调用:

1. -- Function: auto_gen_seq(character)

第二个例子

1. -- DROP FUNCTION auto_gen_seq(character);
2. CREATE OR REPLACE FUNCTION auto_gen_seq("tbName" character)
3. RETURNS character varying AS
4. $BODY$/*
5. 调用示例:
6. SELECT tablename as tableName,
7. 'sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tablename, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', '') AS sqName
8. ,auto_gen_seq(tablename||'') as successFlag,current_date,current_time FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY successFlag,tablename;
9. */
10. DECLARE
11. rd RECORD;
12. seq_sql varchar;
13. flag_str varchar;
14. sq_name varchar;
15. sq_datetime varchar;
16. BEGIN
17. seq_sql:='create table _sequence_table ( id SERIAL not null, code VARCHAR(200) null, increment_num INT8 null, minvalue_num INT8 null, maxvalue_num INT8 null, start_num INT8 null, cache_num INT8 null, cycle_flag VARCHAR(100) null,create_datetime timestamp without time zone,constraint PK__SEQUENCE_TABLE primary key (id) );CREATE UNIQUE INDEX INDEX__sequence_table ON _sequence_table (code);';
18. BEGIN
19. EXECUTE seq_sql;
20. EXCEPTION
21. WHEN OTHERS THEN
22. flag_str:='失败';
23. END;
24. --sq_name:=replace(replace(, 'TB_', ''), 'RH_', '');
25. --sq_name:=replace(replace(sq_name, 'tb_', ''), 'rh_', '');
26. sq_name:='sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', '');
27. /*
28. seq_sql:='drop SEQUENCE '||sq_name||';';
29. BEGIN
30. EXECUTE seq_sql;
31. EXCEPTION
32. WHEN OTHERS THEN
33. flag_str:='失败';
34. END;
35. */
36. seq_sql:='CREATE SEQUENCE '||sq_name||' START 1000000 CACHE 30;';
37. BEGIN
38. EXECUTE seq_sql;
39. EXCEPTION
40. WHEN OTHERS THEN
41. return '失败,创建序列';
42. END;
43. sq_datetime:=to_timestamp(current_date||' '||current_time,'yyyy-mm-dd hh24:mi:ss') ;
44. seq_sql:='INSERT INTO _sequence_table( code,increment_num,minvalue_num,start_num, cache_num,create_datetime) VALUES ( '''||sq_name||''',1,1000000,1000000, 30,'''||sq_datetime||''');';
45. BEGIN
46. EXECUTE seq_sql;
47. EXCEPTION
48. WHEN OTHERS THEN
49. return '失败,插入序列信息';
50. END;
51. return '成功';
52. END;
53. $BODY$
54. LANGUAGE plpgsql VOLATILE STRICT
55. COST 100;
56. ALTER FUNCTION auto_gen_seq(character)
57. OWNER TO postgres;
58. SELECT tablename as tableName,

调用

1. 'sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tablename, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', '') AS sqName
2. ,auto_gen_seq(tablename||'') as successFlag,current_date,current_time FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY successFlag,tablename;

转载自：https://www.iteye.com/blog/panyongzheng-2194815