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