Quick Links

Connectby blues

From:	Mladen Gogala <mgogala(at)vmsinfo(dot)com>
To:	"pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject:	Connectby blues
Date:	2010-05-20 16:58:07
Message-ID:	4BF56A1F.7030500@vmsinfo.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-novice

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
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: 
 
<blockquote><tt>scott=> select * from emp;</tt> 
<tt> empno | ename  |    job    | mgr  |      hiredate       | sal  |
comm | </tt> 
<tt>deptno </tt> 
<tt>-------+--------+-----------+------+---------------------+------+------</tt> 
<tt>+--------</tt> 
<tt>  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450
|      </tt> 
<tt>|     10</tt> 
<tt>  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000
|      </tt> 
<tt>|     10</tt> 
<tt>  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300
|      </tt> 
<tt>|     10</tt> 
<tt>  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800
|      </tt> 
<tt>|     20</tt> 
<tt>  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600
|  300 </tt> 
<tt>|     30</tt> 
<tt>  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250
|  500 </tt> 
<tt>|     30</tt> 
<tt>  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975
|      </tt> 
<tt>|     20</tt> 
<tt>  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 |
1400 </tt> 
<tt>|     30</tt> 
<tt>  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850
|      </tt> 
<tt>|     30</tt> 
<tt>  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000
|      </tt> 
<tt>|     20</tt> 
<tt>  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500
|    0 </tt> 
<tt>|     30</tt> 
<tt>  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100
|      </tt> 
<tt>|     20</tt> 
<tt>  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950
|      </tt> 
<tt>|     30</tt> 
<tt>  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000
|      </tt> 
<tt>|     20</tt> 
<tt>(14 rows)</tt> 
</blockquote>
 
Some of you might even recognize this table, it is frequently used in
the Oracle courses. True enough, connectby function works flawlessly: 
 
<blockquote><tt>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=> 
</tt></blockquote>
 
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: 
<blockquote><tt>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=> </tt> 
</blockquote>
 
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. 
 
<pre class="moz-signature" cols="72">--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
<a class="moz-txt-link-freetext" href="http://www.vmsinfo.com">http://www.vmsinfo.com</a>
The Leader in Integrated Media Intelligence Solutions

</pre>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	6.4 KB

Responses

Re: Connectby blues at 2010-05-20 17:46:49 from Tom Lane

Browse pgsql-novice by date

	From	Date	Subject
Next Message	Tom Lane	2010-05-20 17:46:49	Re: Connectby blues
Previous Message	Ashley Sheridan	2010-05-20 16:26:27	Re: [PHP] Some undefined function errors