Thread: Recursive request ...
I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ?
Benoît Bournon wrote: > I have to make a function that returns a tree with title and link of a > table. > > Recursively, a information depends on a parent information. > > It is to organise a menu with parent dependance. > > How is it possible and faster ? in C ? pl/pgsql or other ? > On 7.3 and later: see contrib/tablefunc. The function is called connectby(). HTH, Joe
On Wed, 2003-07-09 at 15:27, Benoît Bournon wrote: > I have to make a function that returns a tree with title and link of a > table. > > Recursively, a information depends on a parent information. > > It is to organise a menu with parent dependance. > > How is it possible and faster ? in C ? pl/pgsql or other ? If your only concern is speed, go with a C function. This (however) can make backing up / restoring the database to a different machine a little bit cumbersome. You need to be sure to install the modules on the other box manually prior to restoring the database itself.
Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: > I have to make a function that returns a tree with title and link of a > table. > > Recursively, a information depends on a parent information. > > It is to organise a menu with parent dependance. > > How is it possible and faster ? in C ? pl/pgsql or other ? > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
I see connect by in Oracle <br /><br /><br /> ??? is there an equivalent in PostgreSQL or not ??<br /><br /><br /><br />Rajesh Kumar Mallah a écrit:<br /><blockquote cite="mid200307131217.02856.mallah@trade-india.com" type="cite"><pre wrap="">DearBournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions<a class="moz-txt-link-rfc2396E" href="http://www.brasileiro.net:8080/postgres/cookbook/GistBased:contrib/ltreeJoeCelko'sArticleon">" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "</a>Nested Sets & Adjacency Lists" <a class="moz-txt-link-freetext" href="http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html">http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html</a> <a class="moz-txt-link-freetext" href="http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html">http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html</a> <a class="moz-txt-link-freetext" href="http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe">http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe</a> On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: </pre><blockquote type="cite"><pre wrap="">I have to make a functionthat returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote>
BenLaKnet wrote: > I see connect by in Oracle > > ??? is there an equivalent in PostgreSQL or not ?? Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for 7.5. Joe
Joe Conway <mail@joeconway.com> writes: > BenLaKnet wrote: > > I see connect by in Oracle > > ??? is there an equivalent in PostgreSQL or not ?? > > Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for > 7.5. There's a connectby hack in the contrib/tablefunc directory. I haven't used it so I'm not clear on how powerful it is compared to the real deal, but people on one of the pgsql lists seemed to find it useful when it came up in the past. connectby(text relname, text keyid_fld, text parent_keyid_fld, text start_with, int max_depth [, text branch_delim]) - returns keyid, parent_keyid, level, and an optional branch string - requires anonymous compositetype syntax in the FROM clause. See the instructions in the documentation below. -- greg
Thx a lot, I know now that it is possible to do that with pure sql.<br /><br /><br /><br /> Have you the alogorythm, becauseyour link is dead ?<br /><br /><br /> Ben<br /><br /><br /> Rajesh Kumar Mallah a écrit:<br /><blockquote cite="mid200307131217.02856.mallah@trade-india.com"type="cite"><pre wrap="">Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions<a class="moz-txt-link-rfc2396E" href="http://www.brasileiro.net:8080/postgres/cookbook/GistBased:contrib/ltreeJoeCelko'sArticleon">" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "</a>Nested Sets & Adjacency Lists" <a class="moz-txt-link-freetext" href="http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html">http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html</a> <a class="moz-txt-link-freetext" href="http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html">http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html</a> <a class="moz-txt-link-freetext" href="http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe">http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe</a> On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: </pre><blockquote type="cite"><pre wrap="">I have to make a functionthat returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote>
I see connect by in Oracle <br /><br /><br /> ??? it is equivalent in PostgreSQL or not ??<br /><br /><br /> Rajesh KumarMallah a écrit:<br /><blockquote cite="mid200307131217.02856.mallah@trade-india.com" type="cite"><pre wrap="">Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions<a class="moz-txt-link-rfc2396E" href="http://www.brasileiro.net:8080/postgres/cookbook/GistBased:contrib/ltreeJoeCelko'sArticleon">" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "</a>Nested Sets & Adjacency Lists" <a class="moz-txt-link-freetext" href="http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html">http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html</a> <a class="moz-txt-link-freetext" href="http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html">http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html</a> <a class="moz-txt-link-freetext" href="http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe">http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe</a> On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: </pre><blockquote type="cite"><pre wrap="">I have to make a functionthat returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote>