Using column from SELECT INTO in WHERE ANY in Postgres-CodePudding

I have a SELECT INTO query like the following in a procedure :

declare

employee_data record;
item record

begin

select * into employee_data from employees where emp_id=10;

Now I am using a column from the employee_data variable in another query:

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

Here, the notify_users column in employee table is of type jsonb and it is an array like ["2001", "3457"]

When I try to save this query, it is showing a syntax error

error:syntax error at or near employee_data: WHERE E.emp_id = ANY(ARRAY employee_data

How can I use the result in WHERE ANY ?

CodePudding user response：

This should work:

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)  -- assuming notify_users is an array?
  LOOP
     RAISE NOTICE '%', item;
  END LOOP;
END
$do$

The additional keyword ARRAY made no sense. Assuming notify_users is an array, use it as is. See:

IN vs ANY operator in PostgreSQL

A FOR loop works with an implicit cursor. Each row is assigned to item - as declared at the top. The additional INTO notify_users_data made no sense.

Some other minor edits.

Can be simplified further:

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 i.emp_id = ANY(d.notify_users)
  LOOP
     RAISE NOTICE '%', item;
  END LOOP;
END
$do$

CodePudding user response：

If your variable is an array then you can use the UNNEST function to convert array elements to the selected table data. Example:

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

If your variable in this format ["2001", "3457"] then you can firstly use jsonb_array_elements_text function and after then UNNEST function. Example:

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