pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcasting to arrays
5+ messages / 3 participants
[nested] [flat]
* casting to arrays
@ 2003-07-18 21:07 Mike Rylander <miker@n2bb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mike Rylander @ 2003-07-18 21:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: casting to arrays
@ 2003-07-18 21:34 Joe Conway <mail@joeconway.com>
parent: Mike Rylander <miker@n2bb.com>
0 siblings, 2 replies; 5+ messages in thread
From: Joe Conway @ 2003-07-18 21:34 UTC (permalink / raw)
To: miker@n2bb.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: casting to arrays
@ 2003-07-18 22:29 Mike Rylander <miker@n2bb.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 5+ messages in thread
From: Mike Rylander @ 2003-07-18 22:29 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: casting to arrays
@ 2003-07-18 22:47 Joe Conway <mail@joeconway.com>
parent: Mike Rylander <miker@n2bb.com>
0 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2003-07-18 22:47 UTC (permalink / raw)
To: miker@n2bb.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: casting to arrays
@ 2003-07-19 23:11 Greg Stark <gsstark@mit.edu>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 5+ messages in thread
From: Greg Stark @ 2003-07-19 23:11 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: miker@n2bb.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-19 23:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-18 21:07 casting to arrays Mike Rylander <miker@n2bb.com>
2003-07-18 21:34 ` Joe Conway <mail@joeconway.com>
2003-07-18 22:29 ` Mike Rylander <miker@n2bb.com>
2003-07-18 22:47 ` Joe Conway <mail@joeconway.com>
2003-07-19 23:11 ` Greg Stark <gsstark@mit.edu>
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