Representing a one to many join relationship as an array

Hi there,

I'm trying to take some tables and make some a more human friendly
representation of them, via views. The tables look something like
this:

create table person (
id INT,
name TEXT,
address TEXT
);

create table carers (
id INT,
carer TEXT
);

[ignore the poor typing and so on - this is just for the sake of example]

So, if one 'person' had several 'carers', some data might look like:

insert into person values (1, 'fred', '1 first street');
insert into carers values (1, 'john');
insert into carers values (1, 'mary');

So John and Mary are looking after Fred. So far so good.

Now what I'd like to do is create a view which would give me output like:

id | name | address | id | carers
----+------+----------------+----+-----------------
1 | fred | 1 first street | 1 | {'john', 'mary'}

IE the carers column is an array of all the entries in the carers
table with the same 'id'.

It would be kind of bad if a SUBSELECT had to be performed for every
row of the resulting view output :-)

Is there a way to do this, relatively effeciently? I couldn't find
enough detail on the handling of arrays (in this manner) in the
documentation.

Thanks,

Justin

--
justin(at)hawkins(dot)id(dot)au | "Don't sweat it --
http://hawkins.id.au | it's only 1's and 0's"