pg 事务 存储过程_POSTGRESQL 存储过程实战

转了N多的SQL语句,可是自己用时,却到处是坑啊,啊,啊!!!!!!!!!!!!!!!

想写一个获取表中最新ID值.

上代码

CREATETABLE department(

ID INTPRIMARYKEYNOTNULL,

d_code                                    VARCHAR(50),

d_name                                    VARCHAR(50)NOTNULL,

d_parentID                                INTNOTNULLDEFAULT0); --insert into department values(1,'001','office');--insert into department values(2,'002','office',1);

下面要写个存储过程，以获取表中ID的最大值：

dropfunctionf_getNewID(text,text);createorreplacefunctionf_getNewID(myTableNametext,myFeildNametext)returnsintegeras $$declare    mysql text;

myID integer;begin    mysql:='select max( $1 ) from $2';

executemysqlinto myID using myFeildName,myTableName;

ifmyIDisnullormyID=0thenreturn1;

elsereturnmyID+1;

endif;end;$$ language plpgsql;

--大家可以试一下，上面这个是会报错的

--select f_getNewID('department','ID');

--出错！

看了官方文档，人家就是这么用的：

EXECUTE'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'INTO c

USING checked_user, checked_date;

你确定你看清楚了？？？？？

确定你读完读懂了说明书？？？？？

--这个看了？---------------------------------------EXECUTE'SELECT count(*) FROM '|| quote_ident(tabname)

||' WHERE inserted_by = $1 AND inserted <= $2'INTO c

USING checked_user, checked_date;--这个看了？---------------------------------------EXECUTE'UPDATE tbl SET '|| quote_ident(colname)

||' = '|| quote_literal(newvalue)

||' WHERE key = '|| quote_literal(keyvalue);--=============================--好吧， 我改------------------------------------------------------dropfunctionf_getNewID(text,text);createorreplacefunctionf_getNewID(myTableNametext,myFeildNametext)returnsintegeras $$declare    mysql text;

myID integer;begin    mysql:='select max('|| quote_ident(myFeildName)

||') from '|| quote_ident(myTableName);

executemysqlinto myID;

--using myTableName,myFeildName;ifmyIDisnullormyID=0thenreturn1;

elsereturnmyID+1;

endif;end;

$$ language plpgsql;--==============================--漂亮，成功了！

--But Why?

--注意 对象(表名、字段名等)是不可以直接用变量的，要用  quote_ident()

--

-----------------------------------------------------postgres=#selectf_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=#selectf_getnewid('department','id');

f_getnewid------------2(1行记录)

----终于成功了！大小写还有区别吗？？？ --but why? --当在命令行输入

CREATETABLE role(

ID                                        INTPRIMARYKEYNOTNULL,

r_name                                    VARCHAR(50)NOTNULL,

r_paretnID                                INTNOTNULLDEFAULT0

);

--结果在pgAdmin里看到的却是小写的

--同样，如果是在QUERY TOOLS 下用这样的语句创建还是 所有的字体名为小写

--如果我就想大写怎么办？？？？

--要这样写

CREATETABLE "RoleUPER"(

"ID"                                        INTPRIMARYKEYNOTNULL,

r_name                                    VARCHAR(50)NOTNULL,

"r_paretnID"                                INTNOTNULLDEFAULT0

);

--再用大象看看

--可以了！

总结一下：

1、存储过程(FUNCITON)变量可以直接用  || 拼接。上面没有列出，下面给个栗子：

createorreplacefunctionf_getNewID(myTableNametext,myFeildNametext)returnsintegeras $$declare    mysql text;

myID integer;begin    mysql:='select max('||$2||' ) from '||$1;

executemysqlinto myID using myFeildName,myTableName;

ifmyIDisnullormyID=0thenreturn1;

elsereturnmyID+1;

endif;end;

$$ language plpgsql;

2、存储过程的对象不可以直接用变量，要用 quote_ident(objVar)

3、$1  $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后面的不换 结果 ：select max(myTableName) from myFeildname

4、注意：SQL语句中的大写全部会变成小写，要想大写存大，必须要用双引号。