Representing a one to many join relationship as an array - Mailing list pgsql-general

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@hawkins.id.au  |    "Don't sweat it --
http://hawkins.id.au  |  it's only 1's and 0's"