From: Mike Rylander <miker@n2bb.com>
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: casting to arrays
Date: Fri, 18 Jul 2003 18:29:38 -0400
Message-ID: <200307181829.38160.miker@n2bb.com> (raw)
In-Reply-To: <3F1867F3.7000109@joeconway.com>
References: <200307181707.48441.miker@n2bb.com>
<3F1867F3.7000109@joeconway.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: miker@n2bb.com, mail@joeconway.com
Subject: Re: casting to arrays
In-Reply-To: <200307181829.38160.miker@n2bb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox