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