One of my developers asked me for an equivalent to Oracle's "CONNECT BY"
clause. Sure enough, there is a module called "tablefunc" which contains
several overloaded incarnations of the "connectby" function. I installed
some well known tables, usually used to demonstrate the "CONNECT BY"
oracle clause. The relevant table is this one:

scott=> select * from emp;
empno | ename | job | mgr | hiredate | sal | comm |
deptno
-------+--------+-----------+------+---------------------+------+------
+--------
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 |

Some of you might even recognize this table, it is frequently used in
Oracle courses. True enough, connectby function works flawlessly:

scott=> select empno,mgr,level
from connectby('emp','empno','mgr','7839',0)
as t(empno int,mgr int,level int)
scott-> order by level;
empno | mgr | level
-------+------+-------
7839 | | 0
7782 | 7839 | 1
7566 | 7839 | 1
7698 | 7839 | 1
7900 | 7698 | 2
7934 | 7782 | 2
7788 | 7566 | 2
7902 | 7566 | 2
7654 | 7698 | 2
7844 | 7698 | 2
7499 | 7698 | 2
7521 | 7698 | 2
7369 | 7902 | 3
7876 | 7788 | 3
(14 rows)
Time: 3.218 ms
scott=>

My question, however is the following: why is connect by needed at all?
Postgres supports ANSI standard recursive with clause which makes the
following query possible:
scott=> with recursive e(empno,mgr,level) as (
select empno,mgr,0 from emp where empno=7839
union
select emp.empno,emp.mgr,e.level+1
from emp,e
where emp.mgr=e.empno)
select * from e;
empno | mgr | level
-------+------+-------
7839 | | 0
7782 | 7839 | 1
7566 | 7839 | 1
7698 | 7839 | 1
7934 | 7782 | 2
7499 | 7698 | 2
7521 | 7698 | 2
7654 | 7698 | 2
7788 | 7566 | 2
7844 | 7698 | 2
7900 | 7698 | 2
7902 | 7566 | 2
7369 | 7902 | 3
7876 | 7788 | 3
(14 rows)

Time: 2.661 ms
scott=>

It even executes faster than the fancy module, calling a C library
function. Does anybody here use the connectby function and if so, why?


--
http://mgogala.byethost5.com

laden Gogala wrote on 20.05.2010 18:50:
Recursive CTE was introduced in 8.4, the connect_by module has been around much longer.

But I agree: with 8.4 the connect_by module is no longer necessary, the standard CTE is a lot more portable (and even understood by Oracle 11gR2)

Thomas

Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:
[...]


The implementation in tablefunc has been around for longer than
PostgreSQL's native WITH support. I presume it's kept around for
backward compatibility.

-M-

On Thu, 20 May 2010 19:04:47 +0200, Thomas Kellerer wrote:

I am a relatively new user of Postgres. I should have guessed as much. As
far as I am concerned, Postgres starts with version 8.3.8.

Yup, you're right, but it doesn't recognize the keyword "recursive" and
it must go with "UNION ALL", it doesn't work with a simple "UNION". In
addition to that, I cannot use the keyword "level" as a column name. The
same query that works on 11.2 fails miserably on 10.2.0.5:

Connected.
SQL> /
with e(empno,mgr,lev) as (
*
ERROR at line 1:
ORA-32033: unsupported column aliasing


Elapsed: 00:00:00.10
SQL>


I would say that Oracle's support for the recursive CTE is much more
limited than the Postgres support. BTW, what does "CTE" stand for? Oracle
calls it "subquery factoring".
--
http://mgogala.byethost5.com

Mladen Gogala wrote on 20.05.2010 19:56:


Interesting, as (regular) CTEs are available since 9i IIRC

Common Table Expression

On Thu, 20 May 2010 20:24:24 +0200, Thomas Kellerer wrote:

That is true. However, I didn't do much with that.

<...>

This one was recursive. Normal CTEs are supported.

Thanks.




--
http://mgogala.byethost5.com

On 20.05.2010 20:36, Mladen Gogala wrote:
Could be that the term was coined in the Microsoft or Sybase world. MS
SQL Server's documentation is the first place that I noticed it (which
does not need to mean much).

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme wrote on 20.05.2010 20:52:
I don't know if MS created the expression before that, but the term "Common Table Expression" was one of the extensions introduced with SQL 99

Thomas

On 2010-05-20 20:24, Thomas Kellerer wrote:
Not only in Oracle. It was a reserved word in sql92 and in sql99. What's
peculiar is that it does not appear to be a reserved word in sql2003.

/Lennart

[...]

On 2010-05-20 21:44, Thomas Kellerer wrote:
AFAIK IBM (db2) invented the construction and proposed it to the standard.

/Lennart