From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Recursive select
Date: Mon, 29 Oct 2001 14:44:35 -0500 (EST)
Message-ID: <200110291944.OAA22290@blount.mail.mindspring.net> (raw)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Is there any simple select statement that can get all rows
> with MyID = 5 and all its parents?
Simple? No. You can do it directly with SQL if you know how
"deep" you want to recurse. For example, a single level
would be:
SELECT * FROM foo
WHERE myid = 5
OR myid IN
(SELECT parentid FROM foo WHERE myID=5)
while two levels would be:
SELECT * FROM foo
WHERE myid = 5
OR myid IN
(SELECT parentid FROM foo WHERE myID=5)
OR myID in
(SELECT parentID FROM foo WHERE myid in
(SELECT parentID FROM foo WHERE myid=5));
etc. It only gets worse from there. :) For unlimited recursion,
you'll have to have a client slurp all the parent and children
into some sort of lookup table (e.g. a hash) and then loop
through until you've found them all. Here's a small perl example,
assuming you are using DBI, RaiseError is true, and 'myid'
is a unique column:
my $FOO_SQL = "SELECT myid, parentid, 'whateverelse' FROM foo";
my $foo_h = $dbh->prepare($FOO_SQL);
$foo_h->execute();
my %parent;
while(defined($_=$foo_h->fetchrow_arrayref())) {
$parent{$_->[0]}=$_->[1];
}
my %seenit;
my $winner = 5;
{
my $newwinner = $parent{$winner};
last if !defined $newwinner or $seenit{$newwinner}++;
print "Found myid: $winner parentid: $newwinner\n";
$winner = $newwinner;
redo;
}
Doesn't have to perl of course, but it has to be something that
can store variables and perform basic iteration.
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110291441
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO92xbLybkGcUlkrIEQL4PACgx0IvEybczRwcepxjYwWOhEEEwx0AnRNW
dYx/8hHp36sjWhpXyv+cN3sc
=NvbM
-----END PGP SIGNATURE-----
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: greg@turnstep.com
Subject: Re: Recursive select
In-Reply-To: <200110291944.OAA22290@blount.mail.mindspring.net>
* 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