This is PostgreSQL 13.10...
This question is about using UPDATE with a CTE on a VIEW (though I tried eliminating the VIEW and still have the same issue).
I am using a REST API frontend that generates SQL queries for CSV updates using a template like:
WITH cte AS (SELECT '[...CSV data encoded as JSON...]'::json AS data)
UPDATE t SET c1 = _.c1, c2 = _.c2, ...
FROM (SELECT * FROM JSON_POPULATE_RECORDSET(NULL::t, (SELECT data FROM cte))) _;
It seems to be updating all the rows in the table and not just the ones referenced in the CTE:
test=# create table t (tid int, tval text);
CREATE TABLE
test=# insert into t (tid, tval) select generate_series(1,100000), md5(random()::text);
INSERT 0 100000
test=# create view v as select tid as id, tval as val from t;
CREATE VIEW
test=# select count(*) from v;
count
--------
100000
(1 row)
test=# WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
test-# SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte)) _;
id | val
-------+-------
99991 | test3
99992 | test4
(2 rows)
test=# begin;
BEGIN
test=*# WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
test-*# UPDATE v SET val = _.val, id = _.id
test-*# FROM (SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte))) _;
UPDATE 100000
test=*#
test=*# select count(*) from v where val like 'test%';
count
--------
100000
(1 row)
There's no WHERE
clause in the UPDATE
; therefore, all of the rows in v are updated.