Select multiple rows as array

Question

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?

Answer 1

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.

Answer 2

To put data from individual rows into an array, use an array constructor:

DECLARE id_nums int[];  -- assuming cust_id is of type int

id_nums := ARRAY (SELECT cust_id FROM customers WHERE name = cname);

Or the aggregate function array_agg()

id_nums := (SELECT array_agg(cust_id) FROM customers WHERE name = cname);

Or use SELECT INTO for the assignment::

SELECT INTO id_nums
       ARRAY (SELECT cust_id FROM customers WHERE name = cname);