Re: looking for some suggestions - Mailing list pgsql-novice

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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Chansup Byun
> Sent: Friday, May 05, 2006 3:22 PM
> To: pgsql-novice@postgresql.org
> Cc: Chansup.Byun@Sun.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