Message-ID: <3F1867F3.7000109@joeconway.com>
Date: Fri, 18 Jul 2003 14:34:43 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: miker@n2bb.com
Cc: pgsql-sql@postgresql.org
Subject: Re: casting to arrays
References: <200307181707.48441.miker@n2bb.com>
In-Reply-To: <200307181707.48441.miker@n2bb.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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