Thread: connectby documentation

connectby documentation

From
"Daniel Caune"
Date:
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


Re: connectby documentation

From
Michael Fuhr
Date:
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


Re: connectby documentation

From
"Daniel Caune"
Date:

> -----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


Re: connectby documentation

From
PFC
Date:
> 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...


Re: connectby documentation

From
"Daniel Caune"
Date:
> "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


Re: connectby documentation

From
Michael Fuhr
Date:
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


Re: connectby documentation

From
"Daniel Caune"
Date:
> > 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


Re: connectby documentation

From
"Daniel Caune"
Date:
> > > 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


Re: connectby documentation

From
Michael Fuhr
Date:
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


Re: connectby documentation

From
"Daniel Caune"
Date:

> -----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


Re: connectby documentation

From
Michael Fuhr
Date:
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


Re: connectby documentation

From
Jean-Paul Argudo
Date:
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


Re: connectby documentation

From
Achilleus Mantzios
Date:
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



Re: connectby documentation

From
Lucius Seneca
Date:
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