Re: looking for some suggestions

Obe, Regina DND\MIS wrote:
> I think the most efficient way to do this is with an aggregate function.
>
> You can define an aggregate for a varchar and text if one doesn't exist
> in your database - like so
>
> CREATE AGGREGATE sum(
> BASETYPE=text,
> SFUNC=textcat,
> STYPE=text
> );
> ALTER AGGREGATE sum(text) OWNER TO postgres;
>
> Then you can rewrite your sql statement like so
>
> SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
> FROM persons p LEFT JOIN dependents d ON p.person_id = d.parent_id LEFT
> JOIN c.persons c ON d.child_id = c.person_id
> GROUP BY p.fname
>

I followed your suggestion and was able to aggregate child names.

boc=# SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
boc-# FROM persons p
boc-# LEFT JOIN dependents d ON p.person_id = d.parent_id
boc-# LEFT JOIN persons c ON d.child_id = c.person_id
boc-# GROUP BY p.fname;
parent | Children
------------+--------------------
Momi Three |
Dadj Four |
Kidc One |
Dadh Three |
Mome Two | Kidf Two Kidg Two
Momb One | Kidc One
Dadd Two | Kidf Two Kidg Two
Dada One | Kidc One
Kidg Two |
Kidf Two |

Now my next question is how to make sure kids are ordered by ages?

Suppose that I extend the persons table and add birth_date filed, how
can I order child names by age? Is there a way to put such a check in to
the aggregate sum(text) function?

create table persons
(
person_id serial ,
fname varchar(32) ,
birth_date date ,
CONSTRAINT person_pk PRIMARY KEY(person_id)
);

Thanks,

- Chansup

>
>
>
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Chansup Byun
> Sent: Friday, May 05, 2006 3:22 PM
> To: pgsql-novice(at)postgresql(dot)org
> Cc: Chansup(dot)Byun(at)Sun(dot)COM
> Subject: [NOVICE] looking for some suggestions
>
>
> Hi,
>
> I am looking for some suggestions on my test example.
>
> I have two tables: one for persons and the other for dependents table,
> which is shown below. I would like to concatenate all children of the
> same parents in a single string such as:
>
> Children
> -----------------
> Kidc One
> Kidf Two, Kidg Two
>
> Can anyone help me how to do that from the following tables?
>
> I have installed PostgreSQL 8.1.3.
>
> The tables and their records are given below.
>
> create table persons
> (
> person_id serial ,
> fname varchar(32) ,
> CONSTRAINT person_pk PRIMARY KEY(person_id)
> );
>
> create table dependents
> (
> parent_id integer not null,
> child_id integer not null,
> CONSTRAINT dependents_pk PRIMARY KEY(parent_id, child_id)
> );
>
>
> insert into persons(fname) values('Dada One');
> insert into persons(fname) values('Momb One');
> insert into persons(fname) values('Kidc One');
>
> insert into persons(fname) values('Dadd Two');
> insert into persons(fname) values('Mome Two');
> insert into persons(fname) values('Kidf Two');
> insert into persons(fname) values('Kidg Two');
>
> insert into persons(fname) values('Dadh Three');
> insert into persons(fname) values('Momi Three');
>
> insert into persons(fname) values('Dadj Four');
>
> insert into dependents(parent_id, child_id) values('1', '3'); insert
> into dependents(parent_id, child_id) values('2', '3'); insert into
> dependents(parent_id, child_id) values('4', '6'); insert into
> dependents(parent_id, child_id) values('5', '6'); insert into
> dependents(parent_id, child_id) values('4', '7'); insert into
> dependents(parent_id, child_id) values('5', '7');
>
> The following attempt can list all the children but I'm not sure how to
> group them into a single string based on their parents.
>
> SELECT DISTINCT c.fname AS "Children"
> FROM persons p, persons c, dependents d
> WHERE d.parent_id = p.person_id AND
> d.child_id = c.person_id
> ;
>
> Children
> ----------
> Kidc One
> Kidf Two
> Kidg Two
>
>
> Thanks,
>
> - Chansup
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally
> privileged and/or exempt from disclosure pursuant to Massachusetts
> law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and
> delete the material from any computer.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend