Help on constructing a query that matches array

2010/1/19 BlackMage <dsd7872@uncw.edu>:

Hey all,

I need help on constructing a query with arrays that matches the arrays up
as foriegn keys to another table. For example, say that I have two tables,
owners and pets.

Owner Table
owner_id | pet_ids
1             |    {1,2,3}
2             |    {2,3}

Pet Table
pet_ids   |   Pet Type
1            |  Dog
2            |  Cat
3            |  Fish
4            |  Bird

Basically I am trying to create a SELECT query that returns the type of pets
an owner has by matching the pet_ids up. Can anyone help me with this?

You can use the built-in unnest() array function (see
http://www.postgresql.org/docs/8.4/static/functions-array.html) to
convert the array to a set of rows which you can then join in the
standard way. For example:

select o.owner_id, o.pet_id, p.pet_type from
(select owner_id, unnest(pet_ids) as pet_id from owner) as o, pet as p
where p.pet_id = o.pet_id and owner_id=1;

Note: the unnest() function is only defined as standard in postgresql
8.4. If you have an older version, you'll need to define it yourself,
as described here:
http://wiki.postgresql.org/wiki/Array_Unnest

Regards,
Dean