CODEWITHSUNDEEP
sqlarrayspostgresqlplpgsql
user974703
user974703

Reputation: 1653

Select multiple rows as array

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?

Upvotes: 9

Views: 36869

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656311

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

Upvotes: 7

sufleR
sufleR

Reputation: 2973

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.

Upvotes: 14

Related Questions