Re: Help on constructing a query that matches array

2010/1/19 BlackMage <dsd7872(at)uncw(dot)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