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 stackoverflow.com/questions/11731481/… — sufleR
– sufleR, Commented Nov 9, 2012 at 16:39
the alternative to an array might be a loop? Is there a way to specify getting the 'next' result? — user974703
– user974703, Commented Nov 9, 2012 at 16:41

sufleR · Accepted Answer · 2012-11-09 18:55:56Z

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 9, 2012 at 18:55

answered Nov 9, 2012 at 16:55

sufleR

2,99320 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user974703 Over a year ago

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 Over a year ago

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

Erwin Brandstetter Over a year ago

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

sufleR Over a year ago

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

Erwin Brandstetter · Accepted Answer · 2015-02-19 03:11:11Z

7

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

edited Feb 19, 2015 at 3:11

answered Nov 9, 2012 at 18:07

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Select multiple rows as array

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related