Thread: connectby documentation
Hi,
I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ...
equivalence. It seems that PostgreSQL (version >= 7.4) supports a
function connectby that provides similar feature. Unfortunately I don't
find any documentation on that function. Could you please give me a
link on such documentation?
Note: Just an example of the Oracle START WITH ... CONNECT BY PRIOR ...
behaviour.
4 | | | 2 | / \ | 1 3 | Hierarchy dependency
order / \ | | 5 | 6 | |/ | 7 V
SELECT JobId, JobParentId FROM JobDependency START WITH JobParentId IN ( _Root_Datamarts_ ) CONNECT BY PRIOR
JobId= JobParentId
JOBID JOBPARENTID ----- ----------- 2 4 1 2 5 1 7 1 3
2 6 3 7 6
Regards,
--
Daniel
On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: > I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... > equivalence. It seems that PostgreSQL (version >= 7.4) supports a > function connectby that provides similar feature. Unfortunately I don't > find any documentation on that function. Could you please give me a > link on such documentation? connectby() is part of the contrib/tablefunc module. You'll need to install that module and load it into your database. Somebody has made a CONNECT BY patch but the developers have objected to it for various reasons. Search the list archives for discussion. -- Michael Fuhr
> -----Message d'origine----- > De : Michael Fuhr [mailto:mike@fuhr.org] > Envoyé : lundi, mars 13, 2006 11:12 > À : Daniel Caune > Cc : postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: > > I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... > > equivalence. It seems that PostgreSQL (version >= 7.4) supports a > > function connectby that provides similar feature. Unfortunately I don't > > find any documentation on that function. Could you please give me a > > link on such documentation? > > connectby() is part of the contrib/tablefunc module. You'll need > to install that module and load it into your database. > > Somebody has made a CONNECT BY patch but the developers have objected > to it for various reasons. Search the list archives for discussion. > That sounds good. I tried to install PostgreSQL contrib modules on my Linux/Debian distribution: > apt-get install postgresql-contrib (...) The following extra packages will be installed: libpq3 libxml2 postgresql postgresql-7.4 postgresql-client postgresql-client-7.4postgresql-contrib-7.4 (...) 7.4?! Huh... Is there any sources.list a bit more updated? Where can I download PostgreSQL contrib modules. The documentation8.1 doesn't help so much. Where can I find more documentation on available contrib. modules? Thanks, -- Daniel
> 7.4?! Huh... Is there any sources.list a bit more updated? Where can I > download PostgreSQL contrib modules. The documentation 8.1 doesn't help > so much. Where can I find more documentation on available contrib. > modules? gentoo automatically compiles and installs the contribs (you just have to execute the SQL scripts for the pnes you want to put in your database). Maybe you already have them ? trl "locate contrib", who knows...
> "outdated" packets is unfortunately a big issue on Debian. If you want > to have up-to-date apt-packages try > > www.backports.org > > Add one of the mirrors from the list to your sources.list, then run > apt-get update and then try to install again ... > :-) And you'll see, that you can install newer versions than 7.4 :-) > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) Thanks, -- Daniel
On Mon, Mar 13, 2006 at 04:56:39PM -0500, Daniel Caune wrote: > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > works fine... Just a link on the documentation that fully explains how > connectby() works would be great! :-) The contrib package should have installed a file named README.tablefunc. -- Michael Fuhr
> > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > works fine... Just a link on the documentation that fully explains how > > connectby() works would be great! :-) > > The contrib package should have installed a file named README.tablefunc. > You are right. The documentation is located in /usr/share/doc/postgresql-contrib-8.1/ . Wow, that was the quest for the Holy Grail! :-) -- Daniel
> > > Sorry, this is not my day: "apt-get install
postgresql-contrib-8.1"
> > > works fine... Just a link on the documentation that fully
explains
> how
> > > connectby() works would be great! :-)
> >
> > The contrib package should have installed a file named
> README.tablefunc.
> >
>
> You are right. The documentation is located in
> /usr/share/doc/postgresql-contrib-8.1/ .
>
> Wow, that was the quest for the Holy Grail! :-)
>
Huh... It seems that installing the package postgresql-contrib does not
make the work itself.
I provide hereafter a description about how to install the function
connectby (I didn't find such documentation and I don't know where to
write this documentation):
> apt-get install postgresql-contrib-8.1
> emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql
Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I
suggest to modifying only a copy of this file).
> su postgres
> psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql <database>
You can check that the work is done as follows:
> psql <database>
<database> => \df connectby List of functionsSchema | Name | Result data type |
Argument data types
--------+-----------+------------------+--------------------------------
-------------public | connectby | setof record | text, text, text, text, integerpublic | connectby | setof record
| text, text, text, text,
integer, textpublic | connectby | setof record | text, text, text, text, text,
integer, text
I hope that will help another PostgreSQL newbie.
--
Daniel
On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: > I provide hereafter a description about how to install the function > connectby (I didn't find such documentation and I don't know where to > write this documentation): README.tablefunc contains instructions on how to load the module into a database. > > apt-get install postgresql-contrib-8.1 > > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > suggest to modifying only a copy of this file). That shouldn't be necessary unless the package installed the shared objects somewhere other than where the database was expecting. What's the output of "pg_config --libdir --version"? > > su postgres > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql <database> Or, omitting the su, psql -U postgres .... -- Michael Fuhr
> -----Message d'origine----- > De : Michael Fuhr [mailto:mike@fuhr.org] > Envoyé : lundi, mars 13, 2006 19:26 > À : Daniel Caune > Cc : lucius.seneca@gmail.com; postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: > > I provide hereafter a description about how to install the function > > connectby (I didn't find such documentation and I don't know where to > > write this documentation): > > README.tablefunc contains instructions on how to load the module > into a database. > > > > apt-get install postgresql-contrib-8.1 > > > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql > > > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > > suggest to modifying only a copy of this file). > > That shouldn't be necessary unless the package installed the shared > objects somewhere other than where the database was expecting. > What's the output of "pg_config --libdir --version"? > /usr/lib PostgreSQL 8.1.2 > > > su postgres > > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql <database> > > Or, omitting the su, psql -U postgres .... > > -- > Michael Fuhr
On Mon, Mar 13, 2006 at 07:29:44PM -0500, Daniel Caune wrote: > > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > > > suggest to modifying only a copy of this file). > > > > That shouldn't be necessary unless the package installed the shared > > objects somewhere other than where the database was expecting. > > What's the output of "pg_config --libdir --version"? > > /usr/lib > PostgreSQL 8.1.2 Sorry, that should have been --pkglibdir. If it shows the same thing then apparently the contrib module and the database have different notions of where the database's library directory is (assuming that pg_config and the postmaster agree). Did the module and the database come from the same source? If so then the originator's packaging could use improving. -- Michael Fuhr
Daniel Caune a écrit : > Wow, that was the quest for the Holy Grail! :-) Yes I understand. That kind of documentation for a contrib-addon-whatever for PostgreSQL can be tricky sometimes to find.. I just jump on that thread to place a reminder for all those wanting to implement trees in databases, just in case they are still thinking about howto do that. I wroted an article on that topic (in french only sorry : http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id, nested loops and Miguel Sofer's method. This method is explained on OpenACS forums (in english) http://openacs.org/forums/message-view?message_id=18365 The original work of Miguel Sofer (with a PostgreSQL implementation as an example) can be found here: http://www.utdt.edu/~mig/sql-trees/ Be sure to download the tar.gz. on the like "here"... and read his draft. I'm really convinced this method is the best so far. I used it in 3 different projects where I had to implement big trees structures on a table. They all still work with no problem of any kind. Just to let you know in case you missed that ;-) My 2 ¢ -- Jean-Paul Argudo www.Argudo.org www.PostgreSQLFr.org
O Jean-Paul Argudo έγραψε στις Mar 14, 2006 : > Daniel Caune a ιcrit : > > Wow, that was the quest for the Holy Grail! :-) > > Yes I understand. That kind of documentation for a > contrib-addon-whatever for PostgreSQL can be tricky sometimes to find.. > > > I just jump on that thread to place a reminder for all those wanting to > implement trees in databases, just in case they are still thinking about > howto do that. > > I wroted an article on that topic (in french only sorry : > http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id, > nested loops and Miguel Sofer's method. > > This method is explained on OpenACS forums (in english) > > http://openacs.org/forums/message-view?message_id=18365 > > The original work of Miguel Sofer (with a PostgreSQL implementation as > an example) can be found here: > > http://www.utdt.edu/~mig/sql-trees/ > > Be sure to download the tar.gz. on the like "here"... and read his draft. > > I'm really convinced this method is the best so far. I used it in 3 > different projects where I had to implement big trees structures on a > table. They all still work with no problem of any kind. I agree, this genealogical approach is i think the most intuitive/efficient, however this depends on the nature of the intented operation types. One implementation of this (i think) is the ltree contrib module. Haven't worked with this tho. What i actually did for my ultra demanding task (modeling inventory maintenance of 709772 machinery items/parts etc... of ~ 40 vessels), was smth of the type defid | integer | not null default nextval('public.machdefs_defid_seq'::text) parents | integer[] | description | text | machtypeid | integer .......... where parents hold the path from the item's direct parent to its root ancestor, and tree queries are done with a help of a intarray index on parents "machdefs_parents" gist (parents gist__intbig_ops) > > Just to let you know in case you missed that ;-) > > My 2 ’ > > -- > Jean-Paul Argudo > www.Argudo.org > www.PostgreSQLFr.org > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- -Achilleus
Hi "outdated" packets is unfortunately a big issue on Debian. If you want to have up-to-date apt-packages try www.backports.org Add one of the mirrors from the list to your sources.list, then run apt-get update and then try to install again ... :-) And you'll see, that you can install newer versions than 7.4 :-) bye Lucius