背景
使用MySQL的用户可能会比较熟悉这样的用法，更新或删除时可以指定限制更新或删除多少条记录。
达到限定的记录数后不再继续进行下去，而是返回。
delete from tbl where xxx limit 100;
update tbl set xxx=xxx where xxx limit 100;目前PostgreSQL没有类似的语法，但是可以通过其他手段来达到同样的效果。
with语法实现
创建测试表
postgres=# create table t(id int primary key, info text);
CREATE TABLE
postgres=# insert into t select generate_series(1,1000000);
INSERT 0 1000000update | delete limit 用法如下
postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10)
delete from t where id in (select * from t1);
DELETE 10
postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10)
update t set info='new' where id in (select * from t1);
UPDATE 10
postgres=# explain with t1 as (select id from t where id between 1 and 1000 limit 10)
postgres-# update t set info='new' where id in (select * from t1);
QUERY PLAN
---------------------------------------------------------------------------------------
Update on t (cost=4.89..12.93 rows=1 width=38)
CTE t1
-> Limit (cost=0.42..4.44 rows=1 width=4)
-> Index Only Scan using t_pkey on t t_1 (cost=0.42..4.44 rows=1 width=4)
Index Cond: ((id >= 1) AND (id <= 1000))
-> Nested Loop (cost=0.45..8.48 rows=1 width=38)
-> HashAggregate (cost=0.02..0.03 rows=1 width=32)
Group Key: t1.id
-> CTE Scan on t1 (cost=0.00..0.02 rows=1 width=32)
-> Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
Index Cond: (id = t1.id)
(11 rows)使用with模拟必须有PK或者非空UK，否则需要用行号，但是行号来扫就慢了，没走tid scan方法。
postgres=# with t1 as (select ctid from t where id between 1 and 100000 limit 10) delete from t where ctid in (select ctid from t1);
DELETE 10
postgres=# with t1 as (select ctid from t where id between 1 and 100000 limit 10) update t set info='new' where ctid in (select ctid from t1);
UPDATE 10
postgres=# explain with t1 as (select ctid from t where id between 1 and 100000 limit 10) update t set info='new' where ctid in (select ctid from t1);
QUERY PLAN
-----------------------------------------------------------------------------------------
Update on t (cost=129834.64..134784.79 rows=10 width=40)
CTE t1
-> Limit (cost=0.42..0.78 rows=10 width=6)
-> Index Scan using t_pkey on t t_1 (cost=0.42..3178.93 rows=89225 width=6)
Index Cond: ((id >= 1) AND (id <= 100000))
-> Merge Semi Join (cost=129833.86..134784.01 rows=10 width=40)
Merge Cond: (t.ctid = t1.ctid)
-> Sort (cost=129833.49..132308.49 rows=990000 width=10)
Sort Key: t.ctid
-> Seq Scan on t (cost=0.00..14325.00 rows=990000 width=10) -- 这个NODE非常慢
-> Sort (cost=0.37..0.39 rows=10 width=36)
Sort Key: t1.ctid
-> CTE Scan on t1 (cost=0.00..0.20 rows=10 width=36)
(13 rows)这应该也是可以优化的地方，已反馈给社区。
函数+游标实现
do language plpgsql
$$
declare
rowvar record;
cur cursor for select * from t where id between 1 and 1000000 limit 10;
begin
open cur;
loop
fetch cur into rowvar;
if found then
update t set info='new' where current of cur returning * into rowvar; raise notice '%', rowvar;
else
return;
end if;
end loop;
close cur;
end;
$$
;
NOTICE: 00000: (10011,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10012,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10013,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10014,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10015,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10016,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10017,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10018,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10019,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10020,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
DO期待阿里云PostgreSQL扩展支持这个MySQL语法。
