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?

Solution 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.

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

Comments

[sufleR]

there is example of use array in plpgsql stackoverflow.com/questions/11731481/…

[user974703]

the alternative to an array might be a loop? Is there a way to specify getting the 'next' result?

[user974703]

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]

Perfect, that was the problem I had delcared id_nums as an array. I appreciate the thorough and easy to read response!

[Erwin Brandstetter]

FOR loop looks fine, but the SELECT INTO example fails just like the one in the question.

[sufleR]

@ErwinBrandstetter you have right I didn't tested it before. Corrected answer.