Using column from SELECT INTO in WHERE ANY in Postgres

Question

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 ?

Erwin Brandstetter · Answer 1 · 2022-03-14T14:07:19.567

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)  -- ①
    WHERE  employee_data.notify_users ? e.emp_id       -- ②
  LOOP
     RAISE NOTICE '%', item;
  END LOOP;
END
$do$

The additional keyword ARRAY made no sense.

① If notify_users is a Postgres array type, use it as is. See:

IN vs ANY operator in PostgreSQL

② If notify_users is type jsonb of the form ["2000", "3000"], and emp_id is a string type (not a numeric type!), you can use the ? operator as demonstrated.

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

Thanks. When I used notify_user as it is without `ARRAY`, I am getting this error `ANY/ALL (array) requires array on the right side`. This column is of type `jsonb` and hence a JSON array like ["2000", "3000"]. That's why I am trying to convert it into an array — Happy Coder, Mar 13 '22 at 05:53
@HappyCoder: I added a solution for `jsonb`. Please remember to add essential information in your question. — Erwin Brandstetter, Mar 14 '22 at 14:10

score 0 · Answer 2 · answered Mar 12 '22 at 16:19

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)

Using column from SELECT INTO in WHERE ANY in Postgres

2 Answers2