在 Postgres 的 WHERE ANY 中使用 SELECT INTO 中的列

Question

我在过程中有一个如下所示的 SELECT INTO 查询：

declare

employee_data record;
item record

begin

select * into employee_data from employees where emp_id=10;

现在我在另一个查询中使用 employee_data 变量中的列：

FOR item in 
  SELECT CONCAT(E.first_name,' ',E.last_name) AS employee_name, 
  E.email AS employee_email
  INTO notify_users_data
  FROM employee_info E
  WHERE E.emp_id = ANY(ARRAY employee_data.notify_users)
LOOP

这里是 notify_usersemployee 表中的 code> 列的类型为 jsonb ，它是一个类似于 ["2001", "3457"] 的数组

当我尝试保存这个查询时，它显示语法错误

error:employee_data 处或附近的语法错误： WHERE E.emp_id = ANY(ARRAY employee_data

如何在 WHERE ANY 中使用结果？

Answer 1

这应该有效：

DO
$do$
DECLARE
  employee_data record;
  item record;
  -- notify_users_data record;  -- ?
BEGIN
  SELECT * INTO employee_data FROM employees WHERE emp_id = 10;

  FOR item IN
    SELECT concat_ws(' ', e.first_name, e.last_name) AS employee_name  -- makes more sense
         , e.email AS employee_email
    -- INTO   notify_users_data  -- nonsense
    FROM   employee_info e
--  WHERE  e.emp_id = ANY(employee_data.notify_users)  -- ①
    WHERE  employee_data.notify_users ? e.emp_id       -- ②
  LOOP
     RAISE NOTICE '%', item;
  END LOOP;
END
$do$

附加关键字 ARRAY 没有任何意义。

① 如果 notify_users 是 Postgres 数组类型，则按原样使用。请参阅：

• IN 与 PostgreSQL 中的 ANY 运算符

② If notify_users 是 jsonb 形式的 ["2000", "3000"]，而emp_id是字符串类型（不是数字类型！），可以使用? 运算符，如图所示。

FOR 循环 使用隐式游标。每行都分配给 item - 如顶部声明的那样。额外的INTO notification_users_data没有任何意义。

其他一些小的编辑。

可以进一步简化：

DO
$do$
DECLARE
  item record;
BEGIN
  FOR item IN
    SELECT concat_ws(' ', i.first_name, i.last_name) AS employee_name, i.email AS employee_email
    FROM   employee_data d
    JOIN   employee_info i ON d.notify_users ? i.emp_id
  LOOP
     RAISE NOTICE '%', item;
  END LOOP;
END
$do$

Answer 2

如果您的变量是数组，则可以使用 UNNEST 函数将数组元素转换为选定的表数据。
示例：

WHERE E.emp_id in (select t1.pval from unnest(employee_data.notify_users) t1(pval))

如果您的变量采用这种格式 ["2001", "3457"] 那么您可以首先使用 jsonb_array_elements_text 函数，然后使用 UNNEST 函数。
例子：

WHERE E.emp_id in (select jsonb_array_elements_text('["2001", "3457"]'::jsonb)::int4)