pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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-----




view thread (11+ messages)  latest in thread

Message-ID: <200110291944.OAA22290@blount.mail.mindspring.net>
Permalink:  ../200110291944.OAA22290@blount.mail.mindspring.net/
Also on:    postgresql.org/message-id/200110291944.OAA22290@blount.mail.mindspring.net

 · 

reply

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