Select multiple rows as array

arrays, plpgsql, postgresql, sql

Solution

In declare

  DECLARE id_nums bigint[];

in select

id_nums :=  ARRAY(select cust_id from Customers WHERE name = CName);

If you prefer loop use

DECLARE id_num bigint;

FOR id_num in select cust_id from Customers WHERE name = CName LOOP
  your code here
END LOOP;

Read plpgsql control structures in postgresql docs 9.1.

Problem

I have a table where two people may have the same name, but separate IDs. I am given the name, and need to request their IDs. When I use a command like: ``` SELECT id_num INTO cust_id FROM Customers WHERE name=CName; ``` If I use this command on the command line (psql), it returns 2 results (for example). But when I use it as part of an SQL script (PL/pgSQL), it always just grabs the first instance. I tried selecting into `cust_id[]`, but that produced an error. So what is the proper way to select ALL results, and pump them into an array or another easy way to use them?

Original source

Related problems