One of my developers asked me for a Postgres 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 |
|     10
7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |
|     10
7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |
|     10
7369 | SMITH | CLERK     | 7902 | 1980-12-17 00:00:00 | 800 |
|     20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300
|     30
7521 | WARD   | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500
|     30
7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |
|     20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400
|     30
7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |
|     30
7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |
|     20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 |    0
|     30
7876 | ADAMS | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |
|     20
7900 | JAMES | CLERK     | 7698 | 1981-12-03 00:00:00 | 950 |
|     30
7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |
|     20
(14 rows)

Some of you might even recognize this table, it is frequently used in the 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 "connectby" 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? Granted, it's somewhat cleaner to write the "connectby", but not much. Aesthetics alone does not justify its existence. My postgres version is 8.4.3.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions