PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
Update returning NEW|OLD在对账|购票|防纂改|原子操作中的妙用

原作者:digoal/德哥  创作时间:2016-11-25 18:13:04+08  
doudou586 发布于2016-11-28 18:13:04           评论: 0   浏览: 4603   顶: 631  踩: 641 

PostgreSQL update returning NEW|OLD在对账|购票|防纂改|原子操作中的妙用

作者: digoal

日期: 2016-11-25

标签: PostgreSQL , update , returning , NEW , OLD


背景

在数据库中更新记录时,有时为了对账,或者防纂改的目的,需要在更新后立即返回更新前和更新后的值。

例如以set bit为例,假设使用BIT串作为火车的每个位置,每个BIT代表一张票,0表示未售卖,1表示已售卖。

购票时,使用set bit=1的操作,为了确保不出现重复售票的问题,必须确保被set的value以前的值为0,SET后的值为1。

这个动作其实也可以在function中来保证,不过你要扩展FUNCTION的功能,在function中确保set bit前的值为0,set bit后的值为1。

本文要讲的是通过update returning语法来实现类似的对照功能。

其实insert returning也有类似的用法,例如插入时并不知道数据库生成了什么UUID,这个UUID可能是流水号,将来程序要用来做二次确认的搜索。(如运营商的二次确认,或者短信密码,也需要用来作为标识)

update returning语法

目前PostgreSQL支持insert,delete,update的returning。

insert returning 返回的是新插入的值。

delete returning 返回的是被删除的值。

update returning 返回的是更新后的值,不能返回更新前的值,但是有方法可以得到。

或者等阿里云RDS PostgreSQL退出update returning old.column的功能吧。

例子

PostgreSQL 支持delete, update返回删除前的值以及更新后的值.

postgres=# create table test (old text, new text, mod_time timestamp);
CREATE TABLE
postgres=# insert into test values ('old', 'new', now());
INSERT 0 1
postgres=# select * from test ;
 old | new |          mod_time          
-----+-----+----------------------------
 old | new | 2013-01-22 15:36:02.543393
(1 row)

postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning *;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)
UPDATE 1

update returning 返回的是更新后的值.

postgres=# select * from test ;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)

postgres=# delete from test returning *;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)
DELETE 1

delete returning 返回的是删除前的值.

returning 后的子句类似select ... from 中的子句, 所以也支持表达式 :

postgres=# insert into test values ('old', 'new', now());
INSERT 0 1
postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning 1,2,3,old,new,mod_time,old||new;
 ?column? | ?column? | ?column? | old |  new   |          mod_time          | ?column?  
----------+----------+----------+-----+--------+----------------------------+-----------
        1 |        2 |        3 | new | DIGOAL | 2013-01-22 15:39:13.238924 | newDIGOAL
(1 row)
UPDATE 1

update returning 如何返回old.column

方法1, update 中, 如果将一个字段的值赋予给另一个字段, 那会将更新前的值赋予给它, 而不是更新后的值.

postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning 1,2,3,old,new,mod_time,old||new;
 ?column? | ?column? | ?column? | old |  new   |          mod_time          | ?column?  
----------+----------+----------+-----+--------+----------------------------+-----------
        1 |        2 |        3 | new | DIGOAL | 2013-01-22 15:39:13.238924 | newDIGOAL
(1 row)
UPDATE 1

new='DIGOAL', old=new

更新后 :

old = 'new' (new字段更新前的值)

new = 'DIGOAL'

使用这种方法可以返回set_bit前的bit string以及set_bit后的bit string。

方法2, 如果被更新的表有PK,可以使用update from子句, 返回旧值

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be unique
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;
Returns:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

使用这种方法也可以返回set_bit前的bit string以及set_bit后的bit string。

参考



/images/news/2016/pg_bot_banner.jpg


评论:0   浏览: 4603                   顶: 631  踩: 641 

请在登录后发表评论,否则无法保存。


发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)