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?

there is example of use array in plpgsql http://stackoverflow.com/questions/11731481/pl-pgsql-select-into-an-array — sufleR, Nov 09 '12 at 16:39
the alternative to an array might be a loop? Is there a way to specify getting the 'next' result? — user974703, Nov 09 '12 at 16:41

sufleR · Accepted Answer · 2012-11-09T18:55:56.020

14

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.

edited Nov 09 '12 at 18:55

answered Nov 09 '12 at 16:55

sufleR

2,865
17
31

could you give a bit more explanation in regards to the Loop version? id_num is my own variable, cust_id is a column in Customers. I get an error that 'array value must start with "{" or dimension information. The context points to the FOR select statement. – user974703 Nov 09 '12 at 17:05
Perfect, that was the problem I had delcared id_nums as an array. I appreciate the thorough and easy to read response! – user974703 Nov 09 '12 at 17:14
`FOR` loop looks fine, but the `SELECT INTO` example fails just like the one in the question. – Erwin Brandstetter Nov 09 '12 at 18:10
@ErwinBrandstetter you have right I didn't tested it before. Corrected answer. – sufleR Nov 09 '12 at 18:55

Erwin Brandstetter · Answer 2 · 2015-02-19T03:11:11.693

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);

Select multiple rows as array

2 Answers2