用法:
在INSERT INTO或者UPDATE的时候在最后面加上RETURNING colname,PostgreSQL会在插入或者更新数据之后会返回你指定的字段。
postgres=# \d tb3
Table "public.tb3"
Column | Type | Modifiers
--------+-------------------+----------------------------------------------
id | integer | not null default nextval('tb3_id_seq'::regclass)
name | character varying |
**INSERT INTO:**
postgres=# insert into tb3(name) values('aa')returning name;
name
------
aa
(1 row)
INSERT 0 1
postgres=# insert into tb3(name) values('aa')returning id;
id
----
2
(1 row)
INSERT 0 1
postgres=# insert into tb3(name) values('aa')returning id,name;
id | name
----+------
3 | aa
(1 row)
INSERT 0 1
**UPDATE: (返回更新之后的值)**
postgres=# update tb3 tb3 set name='bb' where id=1 returning name;
name
------
bb
(1 row)
UPDATE 1
**RETURNING在FUNCTION中的应用(RETURNING... INTO...)**
postgres=# do language plpgsql $$
postgres$# declare
postgres$# n character varying;
postgres$# begin
postgres$# update tb3 set name='ss' where id=3 returning name into n;
postgres$# raise notice 'n is %',n;
postgres$# insert into tb3(name) values(n);
postgres$# end;
postgres$# $$;
NOTICE: n is ss
DO
postgres=# select * from tb3;
id | name
----+------
2 | aa
1 | bb
3 | ss
4 | ss
(4 rows)
postgres=#