标签
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。
参考
1. http://www.postgresql.org/docs/9.2/static/sql-delete.html
2. http://www.postgresql.org/docs/9.2/static/sql-update.html