SQL 使用来自 CASE EXISTS 的值

Question

Postgres 9.5.3

来自更新不同表中的 JSONB 字段的更大查询的片段(但我认为 JSONB 内容与问题没有任何相关性):

CASE WHEN EXISTS(SELECT role FROM people WHERE result_id = r2.id AND admin = 't')
THEN (elem || jsonb_build_object('admin', true, 'role', role))
ELSE (elem - 'admin' - 'role')
END

基本上，如果记录存在，我希望能够使用该记录中的 role 字段的值。

按照目前的写法，我们得到一个错误 column "role"does not exist

Answer 1

如果您希望 (SELECT role FROM people WHERE result_id = r2.id AND admin = 't') 为 THEN 提供属性 role > 和 ELSE，它不会。

示例:

b=# create table people (result_id int,admin text, role text);
CREATE TABLE
b=# insert into people select 1,'t','one';
INSERT 0 1
b=# create table r2 (id int,elem jsonb);
CREATE TABLE
b=# insert into r2 select 1,'{"admin":false}';
INSERT 0 1
b=#  select CASE WHEN EXISTS(SELECT role FROM people WHERE result_id = r2.id AND admin = 't')
b-# THEN (elem || jsonb_build_object('admin', true, 'role', role))
b-# ELSE (elem - 'admin' - 'role')
b-# END from r2;
ERROR:  column "role" does not exist
LINE 2: ...EN (elem || jsonb_build_object('admin', true, 'role', role))
                                                                 ^
b=# alter table r2 add column role text;
ALTER TABLE
b=#  select CASE WHEN EXISTS(SELECT role FROM people WHERE result_id = r2.id AND admin = 't')
THEN (elem || jsonb_build_object('admin', true, 'role', role))
ELSE (elem - 'admin' - 'role')
END from r2;
             case
-------------------------------
 {"role": null, "admin": true}
(1 row)