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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
