CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Happy Coder
Happy Coder

Reputation: 4692

Using column from SELECT INTO in WHERE ANY in Postgres

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 ?

Upvotes: 0

Views: 198

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

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:

② 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$

Upvotes: 1

Ramin Faracov
Ramin Faracov

Reputation: 3303

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)

Upvotes: 0

Related Questions