Обсуждение: Recursive SQL
I was reading Graeme Birchall's SQL Cookbook at http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM and came across an *amazing* technique called recursive SQL. It's a way to traverse tree-like structures with a single SQL statement. Bizarre stuff--I didn't think this was possible. Anyway, the technique depends upon being able to create a temporary table where some of the rows are SELECTed from that very table during its creation. Essentially, you fill the table with some starting conditions and then use a UNION ALL to keep adding in the new data after each recursive pass. Take a look at page 140 in Graeme's book for more info. I tried this in Postgresql without success. I get syntax errors trying to create the temporary table. Here's some code derived from Graeme's cookbook: create table hierarchy ( pkey char(3) not null, ckey char(3) not null, num int4, primary key(pkey, ckey)); copy hierarchy from stdin; AAA BBB 1 AAA CCC 5 AAA DDD 20 CCC EEE 33 DDD EEE 44 DDD FFF 5 FFF GGG 5 \. Here's my attempt to write recursive SQL code to find the children of 'AAA': create temporary table parent (pkey, ckey) as select pkey, ckey from hierarchy where pkey = 'AAA' union all select c.pkey, c.ckey from hierarchy c, parent p where p.ckey = c.ckey; select pkey, ckey from parent; It appears that Postgresql doesn't like a union inside the create statement. Beyond that, I'm wondering if this technique would even work in Postgresql if it wasn't designed to handle recursive SQL. Any thoughts? Andy Turk andy_turk@hotmail.com ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
"Andy Turk" <andy_turk@hotmail.com> writes: > I was reading Graeme Birchall's SQL Cookbook at > http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM > and came across an *amazing* technique called recursive SQL. Interesting, but I think Birchall has confused some very peculiar (and incorrect) implementation-specific behavior of DB2 with SQL. This is not SQL. Leaving aside a minor quibble about whether the WITH syntax he shows is valid (it's surely not SQL92, although it might be SQL3 if SQL3 ever becomes a standard), the really fundamental problem is that you cannot have a SELECT query that inspects its own output. He claims that in SELECT foo UNION SELECT bar, the "bar" select will somehow see the output of the "foo" select --- and not only that, but will be recursively invoked to see its *own* outputs. I do not believe that any such interpretation can be extracted from the SQL standard. If SQL worked that way, then simple commands likeUPDATE foo SET x = 42 WHERE y = 44 would be infinite loops, because they'd see the new tuples produced by their own action and try to update those, leading to more new tuples, etc etc. He's built a large intellectual edifice on a DB2 bug. regards, tom lane
I have not looked closely at Graeme Birchall's DB2 SQL Cookbook, but Joe Celko has a good section in "SQL for Smarties" on representing trees in relational databases and traversing those trees using standard SQL. He also discusses some of the extensions various vendors have added to make traversing trees (w/o temporary tables) simpler. -=michael=- ***************************************************** * Michael S. Kelly * 4800 SW Griffith Dr., Ste. 202 * Beaverton, OR 97005 USA * voice: (503)644-6106 x122 fax: (503)643-8425 * <michaelk@axian.com> * http://www.axian.com/ ***************************************************** * Axian: Software Consulting and Training ***************************************************** -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, April 19, 2000 8:47 PM To: andy_turk@hotmail.com Cc: pgsql-sql@postgresql.org Subject: Re: Recursive SQL "Andy Turk" <andy_turk@hotmail.com> writes: > I was reading Graeme Birchall's SQL Cookbook at > http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM > and came across an *amazing* technique called recursive SQL. Interesting, but I think Birchall has confused some very peculiar (and incorrect) implementation-specific behavior of DB2 with SQL. This is not SQL. Leaving aside a minor quibble about whether the WITH syntax he shows is valid (it's surely not SQL92, although it might be SQL3 if SQL3 ever becomes a standard), the really fundamental problem is that you cannot have a SELECT query that inspects its own output. He claims that in SELECT foo UNION SELECT bar, the "bar" select will somehow see the output of the "foo" select --- and not only that, but will be recursively invoked to see its *own* outputs. I do not believe that any such interpretation can be extracted from the SQL standard. If SQL worked that way, then simple commands likeUPDATE foo SET x = 42 WHERE y = 44 would be infinite loops, because they'd see the new tuples produced by their own action and try to update those, leading to more new tuples, etc etc. He's built a large intellectual edifice on a DB2 bug. regards, tom lane