From: rawi <only4com@web.de>
To: pgsql-sql@postgresql.org
Subject: recursively isolate pedigrees, based only on person_id, mother_id and father_id
Date: Fri, 28 Aug 2009 08:10:20 -0700 (PDT)
Message-ID: <25191664.post@talk.nabble.com> (raw)
Hello!
Given a table "persons", where the individuals are bound to each other only
via the foreign keys mother_id and father_id as self joins on persons, like
that
create table persons (
id serial primary key,
sex character(1),
father_id integer default null references persons(id) on update cascade on
delete set null,
mother_id integer default null references persons(id) on update cascade on
delete set null
);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(1,'m',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(2,'f',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(8,'m',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(9,'f',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(3,'m',1,2);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(4,'f',8,9);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(5,'f',3,4);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(6,'m',3,4);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(7,'m',8,9);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(10,'m',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(11,'m',10,5);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(12,'f',7,5); --
consanguinity
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(100,'m',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(200,'f',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(300,'m',100,200);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(400,'f',null,null);
INSERT INTO persons(id, sex, father_id,mother_id) VALUES(500,'f',300,400);
These would be the graphs of the 2 families:
http://www.nabble.com/file/p25191664/family1.jpghttp://www.nabble.com/file/p25191664/family2.jpg
I hoped to find a recursive SQL or function, which would extract the WHOLE
family of any given person.id from the table with many families.
After failing to accomplish this with a recursive SQL I found on the web an
advice from Celko (???)
http://www.eggheadcafe.com/conversation.aspx?messageid=29498840&threadid=29498808
to better keep away form such things... :(
Did someone gathered a closer expertise to that?
Thank you very much for any hint!
Regards
Rawi
--
View this message in context: http://www.nabble.com/recursively-isolate-pedigrees%2C-based-only-on-person_id%2C-mother_id-and-fath...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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: only4com@web.de
Subject: Re: recursively isolate pedigrees, based only on person_id, mother_id and father_id
In-Reply-To: <25191664.post@talk.nabble.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