Question 180610 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use array variable in my sql statement in plpgsql

total nube in PL/PGSQL. I would like to define an array of strings and then used that in my SELECT… WHERE In statement but can’t seem to get it to work, help appreciated.

DO $$
DECLARE 
    testArray varchar[] := array['john','lisa'];    
    ids integer[];
BEGIN
    ids = array(select id from tableA where name in (testArray));
    -- this works
    ids = array(select id from tableA where name in ('john','lisa'));
END $$;
Tags:

2

Answers


  1. 0

    Use array_agg to group the ids, and with the operator any you can use the array of names in the where clause, e.g:

    DO $$
DECLARE 
    testArray varchar[] := array['john','lisa'];    
    ids integer[];
BEGIN
    SELECT array_agg(id) INTO ids
    FROM tableA 
    WHERE name = ANY(testArray);
END $$;
    Login or Signup to reply.
  2. 0

    You can use any near of testarray.

    DO $$
DECLARE 
    testArray varchar[] := array['john','lisa'];    
    ids integer[];
BEGIN
    ids = array(select id from tableA where name = any(testArray));
    -- this works
    ids = array(select id from tableA where name in ('john','lisa'));
END $$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search