Re: Recursive SQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: andy_turk(at)hotmail(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Recursive SQL
Date: 2000-04-20 03:47:08
Message-ID: 504.956202428@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

"Andy Turk" <andy_turk(at)hotmail(dot)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 like
UPDATE 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Larry ganga giri 2000-04-20 04:07:44 How to do this in pgsql?
Previous Message Lincoln Yeoh 2000-04-20 02:59:07 Re: Connecting website with SQL-database.....