Connectby blues - Mailing list pgsql-novice

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