Thread: casting to arrays

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

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

Thank you!  This is great news.  Is there a projected release date for 7.4?  
Also, is there a published roadmap, or should I just get on the developers 
list?

Thanks again.
---
Mike Rylander

On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> 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

Mike Rylander wrote:
> Thank you!  This is great news.  Is there a projected release date for 7.4?

Not exactly an officially projected date, but in the past IIRC beta/RC 
has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October.

> Also, is there a published roadmap, or should I just get on the developers 
> list?

The closest thing is the TODO list:  http://developer.postgresql.org/todo.php

But if you want to closely monitor the work actually getting done, 
subscribe to the HACKERS list.

Joe

Joe Conway <mail@joeconway.com> writes:

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

Well there is the int_array_aggregate function in the contrib/intagg
directory. It has to be compiled separately, and it has a few quirks (like the
arrays are zero-based instead of 1-based) but it works more or less, and it
does exactly what you describe.

But the 7.4 stuff should be much cleaner and more flexible, so if you don't
need it right now you're better off waiting.

-- 
greg