View All Attachments for Bug 32248

Attachment #19923
postgresql-7.3.4-r1.ebuild (Update)	text/plain	2003-10-29 01:32:13 UTC	7.40 KB	no flags	Details

Attachment #19924
Patch to PostgreSQL: recursive queries	patch	2003-10-29 01:34:05 UTC	104.59 KB	no flags	Details

Attachment #19925
Patch to PostgreSQL: recursive queries readme	text/html	2003-10-29 01:35:32 UTC	7.88 KB	no flags	Details

<html>
<head>
<title>gppl's pgsql page</title>
<meta http-equiv="Content-type" content="text/html; charset=koi8-r">
<style type="text/css">
a:link{ color: #037; }
a:visited{ color: #025; }
a:active{ color: #036; text-decoration: none; }
.brd{ border-top:solid; border-bottom:solid; border-right:solid; border-left:solid; border-width:1; border-color:#000;}
td.cnt{ color: #d8d7c3; background: #355aa5; border-top:solid; border-bottom:solid; border-right:solid; border-left:solid; border-width:1; border-color:#000; }
</style>
</head>
<body>
<table border=0 cellspacing=0 cellpadding=0 width="100%">
<tr>
	<td align="center" valign="top" style="border-right:solid; border-width:1; border-color: #000000;" bgcolor="#a8a35f">
	<table border=0 cellpadding=0 cellspacing=0 width="100%">
	<tr>
		<td bgcolor="#355aa5" style="border-bottom:solid; border-width:1;">&nbsp;</td>
		<td bgcolor="#355aa5" style="border-bottom:solid; border-width:1;" align="center">
			&nbsp;<p>
			<h1 style="color:#fff;">PGSQL<br>page</h1><p>&nbsp;
		</td>
		<td bgcolor="#355aa5" style="border-bottom:solid; border-width:1;">&nbsp;</td>
	</tr>	
	<tr>
		<td colspan="3" align="center" valign="top">
			<b>Main</b><br>
			 <a href="/">rus</a>
			 <a href="/index.eng.html">eng</a><p>
			<b>Docs:</b><br>
			 <a href="readme.html">readme</a><p>
			<b>Patch itself</b><br>
			hier-0.3<br>
			 <a href="hier-Pg7.2.3-0.3.tar.gz">Pg 7.3.x</a> <a href="hier-Pg7.3-0.3.tar.gz">Pg 7.3.x</a><br>
			 <a href="hier-0.3.tar.gz">all-in-one</a><p>
			hier-0.2<br>
			 <a href="hier-Pg7.2.3-0.2.tar.gz">Pg 7.2.x</a> <a href="hier-Pg7.3-0.2.tar.gz">Pg 7.3.x</a><br>
			 <a href="hier-0.2.tar.gz">all-in-one</a><p>
			hier-0.1<br>
			 <a href="hier-Pg7.2.3-0.1.tar.gz">Pg 7.2.x</a> <a href="hier-Pg7.3-0.1.tar.gz">Pg 7.3.x</a><br>
			 <a href="hier-0.1.tar.gz">all-in-one</a><p>
			<b>Links:</b><br>
			 <a href="http://www.postgresql.org">www.postgresql.org</a><p>
			<b>Subscribe on news:</b><br>
			 <form action="/cgi-bin/elist.pl">
			 <input type=text size=12 name=email><br>
			 <input type=submit value="subscribe">
			 </form>
		</td>	
	</tr>
	</table>
	</td>
	<td bgcolor="#d8d7c3" rowspan="2" valign="top">
    <table border=0 width="100%"><tr><td>&nbsp;</td><td>
<b>WHAT'S THIS</b>

<p>This is a patch which allows PgSQL to perform hierarchical queries like Oracle does.

<p>(c) Evgen Potemkin 2002, &lt; gppl at inbox dot ru &gt;, entirely based on PostgreSQL (http://www.postgresql.org)<br>
Patch itself is distributed under GPL. No warranty of any kind is given, use it at your own risk.<br>


<p><b>INSTALLATION</b>

<p>tar xzf postgresql-7.x.tar.gz<br>
patch -p0 &lt; hier.diff<br>
cd postgresql-7.x/<br>
./configure<br>
... so on

<p><b>SYNOPSIS</b>
	
<p>#create table data (id int4, pnt int4, data varchar(20));

<p>data:
<pre>
+----+-----+-------------+
| id | pnt |    data     |
+----+-----+-------------+
| 0  | 0   | root        |
| 1  | 0   | (1 leaf l1) |
| 2  | 0   | (2 leaf l1) |
| 3  | 0   | (3 leaf l1) |
| 4  | 1   | (11 leaf l2)|
| 5  | 1   | (12 leaf l2)|
| 6  | 1   | (13 leaf l2)|
| 7  | 3   | (31 leaf l2)|
| 8  | 3   | (32 leaf l2)|
+----+-----+-------------+
</pre>
<p># SELECT * FROM data CONNECT BY PRIOR id = pnt START WITH id=0;

<p>output:
<pre>
 id | pnt |    data     | _level_
----+-----+-------------+---------
 0  | 0   | root        | 1
 1  | 0   | (1 leaf l1) | 2
 4  | 1   | (11 leaf l2)| 3
 5  | 1   | (12 leaf l2)| 3
 6  | 1   | (13 leaf l2)| 3
 2  | 0   | (2 leaf l1) | 2
 3  | 0   | (3 leaf l1) | 2
 7  | 3   | (31 leaf l2)| 3
 8  | 3   | (32 leaf l2)| 3
</pre>                        
<p><b>DESCRIPTION</b>
                        
<p>Hierarchical query.     

<p>Lets the tree looks like following: 
<pre>
                          (root)
                        /   |    \
                      /     |      \
             (1 leaf l1) (2 leaf l1) (3 leaf l1)
            /    |    \                   |     \
          /      |      \                 |       \
(11 leaf l2) (12 leaf l2) (13 leaf l2) (31 leaf l2) (32 leaf l2)
</pre><br>
so lets define hierarchical query as query which return us a set like:
<pre>
+------------+
|root        |
|(1 leaf l1) |
|(11 leaf l2)|
|(12 leaf l2)|
|(13 leaf l2)|
|(2 leaf l1) |
|(3 leaf l1) |
|(31 leaf l2)|
|(32 leaf l2)|
+------------+
</pre><br>
i.e. first is root,then first child of root, 
then first child of first child, if any, or second child otherwise, ... so on.

<p><b>REASON</b>.

<p>Often hierarchical queries are made by adding special field which stores a position
of a row in the tree, and then make "SELECT ... ORDER BY 'hier_position_field'" 
over the table. weakness of such approach is that we can't sort leaves in some 
custom order.

<p>This patch is based on internal PG's query processing and havn't such disadvantage.

<p><b>SYNTAX</b>.
<pre>
SELECT ... FROM ... [ WHERE condition ] hier_clause
  [ HAVING condition [, ...]] [ ORDER BY ... ] [ LIMIT ... ] [ OFFSET ... ]

hier_clause: connect_clause start_with_clause
		| start_with_clause connect_clause
		| /* EMPTY */
		;
connect_clause: CONNECT BY PRIOR c_expr qual_Op c_expr
		| CONNECT BY c_expr qual_Op PRIOR c_expr
		;
start_with_clause: START WITH a_expr
		;
</pre><br>
... means the same as usual SELECT<br>
qual_Op is an operator.<br>
c_expr is a column name or function call<br>
expr is a WHERE-like expression<br>

<ul>
<li>WHERE condition
<blockquote>only rows which pass condition will be used
	
</blockquote>
<li>CONNECT BY PRIOR c_expr qual_Op c_expr <br>
CONNECT BY c_expr qual_Op PRIOR c_expr 
<blockquote>	expression next to PRIOR is evaluated against parent tuple, another c_expr - against
	child tuple. qual_Op is an operator for comparation, if results of evaluation
	is equal then tuples will be connected.<br>
	qual_Op is not only '=', it may be any comparation operator for types of given expressions.	<br>
	for cases with different parent and child expressions: operator's function is called
		op_func(child_expression, parent_expression).<br>
	for user-defined operators: operator's function must return 0 if values aer equal, 
	  !0 otherwise.<br>
	EX.: CONNECT BY PRIOR id = pnt<br>
	rows will be connected by parent 'id' and child 'pnt' columns.<br>
	i.e. if in some row (A) column 'pnt' is equal to 'id' column of other row (B)<br>
	means that row A is a child of B.<br>

</blockquote>
<li>START WITH expr
<blockquote>rows for which expr is true will be treaten as root rows and other will 
	be connected to them.

</blockquote>
<li>HAVING condition
<blockquote>used to qualifying rows after building hierarchy. only which satisfy condition will be returned.

</blockquote>
<li>ORDER BY ...
<blockquote>all childs of any parent will be ordered in choosen order. rows can't be ordered by _level_
	within same select where they're connected, use 
 	SELECT ... FROM (SELECT ... CONNECT ...) p ORDER BY _level_
	construction.
</ul>
<b>Warn!</b> Aggregates can't be used with CONNECT BY clause.

<p><b>RESULTS</b>.

<p>query return sets like discribed in synopsis. '_level_' is additional column which 
added only in case of hierarchical query.
it means deep of row in the tree, starts from 1.

<p>Difference with Oracle's hierarchical queries:
	WHERE clause evaluated BEFORE connecting.
	for qualifying rows after they been connected use HAVING.

<p><b>MISC</b>.
<p>Subqueries, views are supported. <br>
initdb is required<br>
Some regression test also exists (make check).<br>
Feedback is welcome:)
<p><b>CHANGES</b>.
<p>v 0.3<br>
<ul>
<li>fixed bugs which prevented of using cursors
<li>fixed HAVING bug
<li>fixed typos in readme
</ul>
<p>v 0.2<br>
<ul>
<li>fixed bug which prevents selecting over 1024 rows
<li>fixed troubles with non-numeric type of columns on which connect is performed
<li>speed imrovement 
</ul>
<p>v 0.1<br>
<ul>
<li>first release.
</ul>
</td><td>&nbsp;</td></tr>
<tr>
	<td>&nbsp;</td>
	<td align="center">
		<hr size=1 noshade><font size="-1">(c) evgen 2002/3, email &lt; gppl at inbox dot ru &gt; </font><br>
	</td>
	<td>&nbsp;</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>

Attachment #19963
postgresql-7.3.4-r1.ebuild (Update)	text/plain	2003-10-30 02:21:43 UTC	7.53 KB	no flags	Details

Attachment #20007
postgresql-7.3.4-r1.ebuild (Update)	text/plain	2003-10-31 03:17:06 UTC	7.65 KB	no flags	Details