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

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
       



-----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.