Re: casting to arrays

Mike Rylander wrote:
> I have a rather odd table structure that I would like to simplify to be a view
> (for some definition of simplify). The current idea I have is to shovel
> values from multiple rows in one table into an array in the view. The tables
> look something like this:
>
<snip>
>
> Is anything like this possible? I know this may not be good form, but
> unfortunately (or perhaps fortunately, since it means I have a job) there are
> business reasons for this, supporting old apps and such.
>

Not possible in current releases, but it will be in 7.4 (about to start
beta). It looks like this:

create table person (id integer, name varchar);
insert into person values(1,'Bob');
insert into person values(2,'Sue');

create table stuff (person_id integer, stuff_name text);
insert into stuff values(1,'chair');
insert into stuff values(1,'couch');
insert into stuff values(1,'lamp');
insert into stuff values(2,'table');
insert into stuff values(2,'shirt');

create or replace view person_with_stuff as select p.id as id, p.name as
name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
as stuff from person p;

regression=# select * from person_with_stuff;
id | name | stuff
----+------+--------------------
1 | Bob | {chair,couch,lamp}
2 | Sue | {table,shirt}
(2 rows)

HTH,

Joe