Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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"&nbsp; clause. Sure enough, there is a module called "tablefunc"
which contains&nbsp; several overloaded incarnations of the "connectby"
function. I installed&nbsp; some well known tables, usually used to
demonstrate the "CONNECT BY" <br>
oracle clause. The relevant table is this one:<br>
<br>
<blockquote><font color="#3333ff"><tt>scott=&gt; select * from emp;</tt><br>
  <tt>&nbsp;empno | ename&nbsp; |&nbsp;&nbsp;&nbsp; job&nbsp;&nbsp;&nbsp; | mgr&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; hiredate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | sal&nbsp; |
comm | </tt><br>
  <tt>deptno </tt><br>
  <tt>-------+--------+-----------+------+---------------------+------+------</tt><br>
  <tt>+--------</tt><br>
  <tt>&nbsp; 7782 | CLARK&nbsp; | MANAGER&nbsp;&nbsp; | 7839 | 1981-06-09 00:00:00 | 2450
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 10</tt><br>
  <tt>&nbsp; 7839 | KING&nbsp;&nbsp; | PRESIDENT |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 1981-11-17 00:00:00 | 5000
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 10</tt><br>
  <tt>&nbsp; 7934 | MILLER | CLERK&nbsp;&nbsp;&nbsp;&nbsp; | 7782 | 1982-01-23 00:00:00 | 1300
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 10</tt><br>
  <tt>&nbsp; 7369 | SMITH&nbsp; | CLERK&nbsp;&nbsp;&nbsp;&nbsp; | 7902 | 1980-12-17 00:00:00 |&nbsp; 800
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 20</tt><br>
  <tt>&nbsp; 7499 | ALLEN&nbsp; | SALESMAN&nbsp; | 7698 | 1981-02-20 00:00:00 | 1600
|&nbsp; 300 </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7521 | WARD&nbsp;&nbsp; | SALESMAN&nbsp; | 7698 | 1981-02-22 00:00:00 | 1250
|&nbsp; 500 </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7566 | JONES&nbsp; | MANAGER&nbsp;&nbsp; | 7839 | 1981-04-02 00:00:00 | 2975
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 20</tt><br>
  <tt>&nbsp; 7654 | MARTIN | SALESMAN&nbsp; | 7698 | 1981-09-28 00:00:00 | 1250 |
1400 </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7698 | BLAKE&nbsp; | MANAGER&nbsp;&nbsp; | 7839 | 1981-05-01 00:00:00 | 2850
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7788 | SCOTT&nbsp; | ANALYST&nbsp;&nbsp; | 7566 | 1987-04-19 00:00:00 | 3000
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 20</tt><br>
  <tt>&nbsp; 7844 | TURNER | SALESMAN&nbsp; | 7698 | 1981-09-08 00:00:00 | 1500
|&nbsp;&nbsp;&nbsp; 0 </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7876 | ADAMS&nbsp; | CLERK&nbsp;&nbsp;&nbsp;&nbsp; | 7788 | 1987-05-23 00:00:00 | 1100
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 20</tt><br>
  <tt>&nbsp; 7900 | JAMES&nbsp; | CLERK&nbsp;&nbsp;&nbsp;&nbsp; | 7698 | 1981-12-03 00:00:00 |&nbsp; 950
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 30</tt><br>
  <tt>&nbsp; 7902 | FORD&nbsp;&nbsp; | ANALYST&nbsp;&nbsp; | 7566 | 1981-12-03 00:00:00 | 3000
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </tt><br>
  <tt>|&nbsp;&nbsp;&nbsp;&nbsp; 20</tt><br>
  <tt>(14 rows)</tt><br>
  </font></blockquote>
<br>
Some of you might even recognize this table, it is frequently used in
the Oracle courses. True enough, connectby function works flawlessly:<br>
<br>
<blockquote><font color="#3333ff"><tt>scott=&gt; select empno,mgr,level<br>
from connectby('emp','empno','mgr','7839',0)<br>
as t(empno int,mgr int,level int)<br>
scott-&gt; order by level;<br>
&nbsp;empno | mgr&nbsp; | level <br>
-------+------+-------<br>
&nbsp; 7839 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
&nbsp; 7782 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7566 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7698 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7900 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7934 | 7782 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7788 | 7566 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7902 | 7566 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7654 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7844 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7499 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7521 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7369 | 7902 |&nbsp;&nbsp;&nbsp;&nbsp; 3<br>
&nbsp; 7876 | 7788 |&nbsp;&nbsp;&nbsp;&nbsp; 3<br>
(14 rows)<br>
Time: 3.218 ms<br>
scott=&gt; <br>
  </tt></font></blockquote>
<br>
My question, however is the following: why is "connectby" needed at
all?&nbsp; Postgres supports ANSI standard recursive "WITH" clause which
makes the following query possible:<br>
<blockquote><font color="#3333ff"><tt>scott=&gt; with recursive
e(empno,mgr,level) as (<br>
select empno,mgr,0 from emp where empno=7839<br>
union<br>
select emp.empno,emp.mgr,e.level+1<br>
from emp,e<br>
where emp.mgr=e.empno)<br>
select * from e;<br>
&nbsp;empno | mgr&nbsp; | level <br>
-------+------+-------<br>
&nbsp; 7839 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
&nbsp; 7782 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7566 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7698 | 7839 |&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
&nbsp; 7934 | 7782 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7499 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7521 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7654 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7788 | 7566 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7844 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7900 | 7698 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7902 | 7566 |&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
&nbsp; 7369 | 7902 |&nbsp;&nbsp;&nbsp;&nbsp; 3<br>
&nbsp; 7876 | 7788 |&nbsp;&nbsp;&nbsp;&nbsp; 3<br>
(14 rows)<br>
  <br>
Time: 2.661 ms<br>
scott=&gt; </tt></font><br>
</blockquote>
<br>
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.<br>
<br>
<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: unknown_filename
Description: text/html (6.4 KB)

Responses

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group