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 · Accepted Answer · 2022-03-14 14:07:19Z

1

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$

edited Mar 14, 2022 at 14:07

answered Mar 12, 2022 at 16:05

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Happy Coder Over a year ago

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

Erwin Brandstetter Over a year ago

@HappyCoder: I added a solution for jsonb. Please remember to add essential information in your question.

Ramin Faracov · Accepted Answer · 2022-03-12 16:19:58Z

0

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)

answered Mar 12, 2022 at 16:19

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using column from SELECT INTO in WHERE ANY in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related