Content-Type: text/plain;
  charset="us-ascii"
From: Mike Rylander <miker@n2bb.com>
Reply-To: miker@n2bb.com
Organization: N2 Broadband
To: pgsql-sql@postgresql.org
Subject: casting to arrays
Date: Fri, 18 Jul 2003 17:07:48 -0400
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307181707.48441.miker@n2bb.com>

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:

create table person (
  id  serial,
  name  varchar
);

create table stuff (
  person_id references person (id) on delete restrict,
  stuff_name varchar
);

The view would go something like:

create view person_with_stuff as
  select p.id as id,
           p.name as name,
           ( select s.stuff_name
             from stuff
             where s.person_id = p.id
           )::varchar[] from person p;

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.

Thanks in advance!
-- 
Mike Rylander