从值引用进行postgreSQL更新

Question

需要使用"|&"；。是否有任何解决方案可以在值更新查询中进行引用？

update permission as p set
"access" = pu.access
from (values
(1, 'tags', p.access || '{"read": false, "create": false, "delete": false, "update": false}'::jsonb),
(1, 'categories', p.access || '{"delete": false, "update": false}'::jsonb)
) as pu(roleId, sectionName, access)
where p."roleId" = pu.roleId and p."sectionId" = (select id from permission_section where sectionName = pu.sectionName);

错误的部分是：

p.access || '{"read": false, "create": false, "delete": false, "update": false}'::jsonb)

这里有一个错误：

[42P01] ERROR: invalid reference to FROM-clause entry for table "p" 
There is an entry for table "p", but it cannot be referenced from this part of the  of the query.

Answer 1

将连接移动到SET部分，而不是VALUES。类似这样的东西：

UPDATE permission AS P 
SET "access" = p.access || pu.access -- concat over here
FROM
( VALUES ( 1, 'tags', '{"read": false, "create": false, "delete": false, "update": false}' :: jsonb )
, ( 1, 'categories', '{"delete": false, "update": false}' :: jsonb ) 
) AS pu ( roleId, sectionName, access ) 
WHERE
P."roleId" = pu.roleId 
AND P."sectionId" = ( SELECT ID FROM permission_section WHERE sectionName = pu.sectionName );