本文实例讲述了postgreSQL存储过程用法。分享给大家供大家参考，具体如下：
转了N多的SQL语句,可是自己用时,却到处是坑啊,啊,啊!!!!!!!!!!!!!!!
想写一个获取表中最新ID值.
上代码
|
1
2
3
4
5
6
7
8
|
CREATE TABLE department( ID INT PRIMARY KEY NOT NULL, d_code VARCHAR(50), d_name VARCHAR(50) NOT NULL, d_parentID INT NOT NULL DEFAULT 0);--insert into department values(1,'001','office');--insert into department values(2,'002','office',1); |
下面要写个存储过程，以获取表中ID的最大值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
drop function f_getNewID(text,text);create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$declare mysql text; myID integer;begin mysql:='select max( $1 ) from $2'; execute mysql into myID using myFeildName,myTableName; if myID is null or myID=0 then return 1; else return myID+1; end if;end;$$ language plpgsql;--大家可以试一下，上面这个是会报错的--select f_getNewID('department','ID');--出错！ |
看了官方文档，人家就是这么用的：
|
1
2
3
|
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; |
你确定你看清楚了？？？？？
确定你读完读懂了说明书？？？？？
|
1
2
3
4
5
6
7
|
--这个看了？---------------------------------------EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
--这个看了？---------------------------------------EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);--=============================--好吧， 我改------------------------------------------------------drop function f_getNewID(text,text);create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$declare mysql text; myID integer;begin mysql:='select max(' || quote_ident(myFeildName) ||') from ' || quote_ident(myTableName); execute mysql into myID; --using myTableName,myFeildName; if myID is null or myID=0 then return 1; else return myID+1; end if;end;$$ language plpgsql;--==============================--漂亮，成功了！--But Why?--注意 对象（表名、字段名等）是不可以直接用变量的，要用 quote_ident()-------------------------------------------------------postgres=# select f_getnewid('department','ID');--错误: 字段 "ID" 不存在--第1行select max("ID") from department ^--查询: select max("ID") from department--背景: 在EXECUTE的第10行的PL/pgSQL函数f_getnewid(text,text)--===============================--什么情况，ID怎么会有双引号，引号，号，号？？？------------------------------------------------------------这里要感谢大神：权宗亮@飞象数据--改成这样：postgres=# select f_getnewid('department','id'); f_getnewid------------ 2(1 行记录)----终于成功了！大小写还有区别吗？？？ --but why? --当在命令行输入CREATE TABLE role( ID INT PRIMARY KEY NOT NULL, r_name VARCHAR(50) NOT NULL, r_paretnID INT NOT NULL DEFAULT 0);--结果在pgAdmin里看到的却是小写的 |
|
1
2
3
4
5
6
7
8
9
|
--同样，如果是在QUERY TOOLS 下用这样的语句创建还是 所有的字体名为小写--如果我就想大写怎么办？？？？--要这样写CREATE TABLE "RoleUPER"( "ID" INT PRIMARY KEY NOT NULL, r_name VARCHAR(50) NOT NULL, "r_paretnID" INT NOT NULL DEFAULT 0);--再用大象看看 |
可以了！
总结一下：
1、存储过程（FUNCITON）变量可以直接用 || 拼接。上面没有列出，下面给个栗子：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$declare mysql text; myID integer;begin mysql:='select max('|| $2 || ' ) from '||$1; execute mysql into myID using myFeildName,myTableName; if myID is null or myID=0 then return 1; else return myID+1; end if;end;$$ language plpgsql; |
2、存储过程的对象不可以直接用变量，要用 quote_ident(objVar)
3、$1 $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后面的不换 结果 ：
|
1
|
select max(myTableName) from myFeildname |
4、注意：SQL语句中的大写全部会变成小写，要想大写存大，必须要用双引号。
附：一个完整postgreSQL 存储过程示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
CREATE OR REPLACE FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int) RETURNS integer AS$BODY$DECLARE v_start_hour character varying; v_end_hour character varying; v_start_time character varying; v_end_time character varying; v_start_datetime timestamp; v_end_datetime timestamp; v_type int := 0; v_rtn int;/* v_test9_count int; v_test9_success int; v_runningtime double precision; v_availablerate double precision; */BEGIN-- hour = even, minute > 30-- exists--if i_type = 1 thenSELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour; --two hours agoSELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour; --get 'hour' of current timeselect v_start_hour || ':30' into v_start_time;select v_end_hour || ':30' into v_end_time;select mt_f_avl_oee_period_e(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;elseSELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '3 hours' ) into v_start_hour;SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;select v_start_hour || ':30' into v_start_time;select v_end_hour || ':00' into v_end_time;select mt_f_avl_oee_period_midnight(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;end if;RETURN 1;EXCEPTIONWHEN others THEN RAISE;RETURN 0;END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;ALTER FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int) OWNER TO postgres; |
希望本文所述对大家PostgreSQL程序设计有所帮助。
原文链接：https://www.cnblogs.com/ssqhan/p/7289931.html
