pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconnectby documentation
14+ messages / 6 participants
[nested] [flat]
* connectby documentation
@ 2006-03-13 15:37 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 16:12 ` Re: connectby documentation Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Caune @ 2006-03-13 15:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 15:37 connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-13 16:12 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2006-03-13 16:12 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
@ 2006-03-13 21:31 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 21:39 ` Re: connectby documentation Lucius Seneca <peter.weinzierl@gmail.com>
2006-03-13 21:45 ` Re: connectby documentation PFC <lists@peufeu.com>
0 siblings, 2 replies; 14+ messages in thread
From: Daniel Caune @ 2006-03-13 21:31 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
> -----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.4 postgresql-contrib-7.4
(...)
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?
Thanks,
--
Daniel
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 21:31 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-13 21:39 ` Lucius Seneca <peter.weinzierl@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Lucius Seneca @ 2006-03-13 21:39 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 21:31 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-13 21:45 ` PFC <lists@peufeu.com>
1 sibling, 0 replies; 14+ messages in thread
From: PFC @ 2006-03-13 21:45 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
> 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...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
@ 2006-03-13 21:56 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 22:53 ` Re: connectby documentation Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Caune @ 2006-03-13 21:56 UTC (permalink / raw)
To: lucius.seneca@gmail.com; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
> "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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 21:56 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-13 22:53 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2006-03-13 22:53 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
@ 2006-03-13 23:02 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 11:45 ` Re: connectby documentation Jean-Paul Argudo <jean-paul@argudo.org>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Caune @ 2006-03-13 23:02 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: lucius.seneca@gmail.com; pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 23:02 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-14 11:45 ` Jean-Paul Argudo <jean-paul@argudo.org>
2006-03-14 13:30 ` Re: connectby documentation Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jean-Paul Argudo @ 2006-03-14 11:45 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 23:02 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 11:45 ` Re: connectby documentation Jean-Paul Argudo <jean-paul@argudo.org>
@ 2006-03-14 13:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 14+ messages in thread
From: Achilleus Mantzios @ 2006-03-14 13:30 UTC (permalink / raw)
To: Jean-Paul Argudo <jean-paul@argudo.org>; +Cc: Daniel Caune <daniel.caune@ubisoft.com>; Michael Fuhr <mike@fuhr.org>; lucius.seneca@gmail.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
@ 2006-03-13 23:38 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 00:25 ` Re: connectby documentation Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Caune @ 2006-03-13 23:38 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; Michael Fuhr <mike@fuhr.org>; +Cc: lucius.seneca@gmail.com; pgsql-sql
> > > 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 functions
Schema | Name | Result data type | Argument data types
--------+-----------+------------------+--------------------------------
-------------
public | connectby | setof record | text, text, text, text, integer
public | connectby | setof record | text, text, text, text,
integer, text
public | connectby | setof record | text, text, text, text, text,
integer, text
I hope that will help another PostgreSQL newbie.
--
Daniel
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-13 23:38 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-14 00:25 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2006-03-14 00:25 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
@ 2006-03-14 00:29 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 01:02 ` Re: connectby documentation Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Caune @ 2006-03-14 00:29 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: lucius.seneca@gmail.com; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: connectby documentation
2006-03-14 00:29 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-14 01:02 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2006-03-14 01:02 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-03-14 13:30 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-13 15:37 connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 16:12 ` Michael Fuhr <mike@fuhr.org>
2006-03-13 21:31 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 21:39 ` Lucius Seneca <peter.weinzierl@gmail.com>
2006-03-13 21:45 ` PFC <lists@peufeu.com>
2006-03-13 21:56 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-13 22:53 ` Michael Fuhr <mike@fuhr.org>
2006-03-13 23:02 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 11:45 ` Jean-Paul Argudo <jean-paul@argudo.org>
2006-03-14 13:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-03-13 23:38 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 00:25 ` Michael Fuhr <mike@fuhr.org>
2006-03-14 00:29 Re: connectby documentation Daniel Caune <daniel.caune@ubisoft.com>
2006-03-14 01:02 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox