Обсуждение: 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@hawkins.id.au  |    "Don't sweat it --
http://hawkins.id.au  |  it's only 1's and 0's"

Hi,

On Thu, 20 Feb 2003 13:33:28 +1030 (CST)
Justin Hawkins <justin@hawkins.id.au> wrote:

>
> 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.

One solution would be creating an aggregate, which sums up all carers.
You would start with empty array and add item to item to it with
the agregate. (See manual for creating aggregate)
You arrange the select so you have multiple lines and
group them with the aggregate over carers.

HTH
Tino Wildenhain