PostgreSQL returning 插入,更新,删除反馈数据

吴贺华

　　文档出处：http://netkiller.github.io/postgresql/index.html

2. returning



例 4.3. PostgreSQL insert returning

test=# create table account(id serial,name varchar,pass varchar);
NOTICE:  CREATE TABLE will create implicit sequence "account_id_seq" for serial column "account.id"
CREATE TABLE
test=# insert into account(name,pass) values('neo','chen') returning id;
id
----
1
(1 row)
INSERT 0 1

　　returning p1,p2...

test=# insert into account(name,pass) values('jam','123'),('john','456') returning id, name;
id | name
----+------
2 | jam
3 | john
(2 rows)
INSERT 0 2

　　returning *

test=# insert into account(name,pass) values('sam',md5('123')),('ivan',md5('456')),('lily',md5('789')) returning *;
id | name |               pass
----+------+----------------------------------
4 | sam  | 202cb962ac59075b964b07152d234b70
5 | ivan | 250cf8b51c773f3f8dc8b4be867a9a02
6 | lily | 68053af2923e00204c3ca7c6a3150cf7
(3 rows)
INSERT 0 3





例 4.4. PostgreSQL update returning

test=# update account set pass = md5(pass) where id=3 returning id,pass;
id |               pass
----+----------------------------------
3 | 250cf8b51c773f3f8dc8b4be867a9a02
(1 row)
UPDATE 1


test=# update account set pass = md5(pass) where id < 3 returning *;
id | name |               pass
----+------+----------------------------------
1 | neo  | a1a8887793acfc199182a649e905daab
2 | jam  | 202cb962ac59075b964b07152d234b70
(2 rows)
UPDATE 2





例 4.5. PostgreSQL delete returning

test=# delete from account where id=6 returning id,name;
id | name
----+------
6 | lily
(1 row)
DELETE 1


test=# delete from account where id<6 returning *;
id | name |               pass
----+------+----------------------------------
4 | sam  | 202cb962ac59075b964b07152d234b70
5 | ivan | 250cf8b51c773f3f8dc8b4be867a9a02
3 | john | 250cf8b51c773f3f8dc8b4be867a9a02
1 | neo  | a1a8887793acfc199182a649e905daab
2 | jam  | 202cb962ac59075b964b07152d234b70
(5 rows)
DELETE 5