Thread: connectby(... pos_of_sibling)
Hi all,
I have a question about connectby():
Is it possible to have the siblings sorted ?
pos gives the order of the siblings:
--------------------------8<-------------------------
CREATE TABLE nav (
id_nav serial primary key,
id2_nav integer references nav,
nav text not null,
pos integer not null
);
COPY nav (id_nav, id2_nav, nav, pos) FROM stdin;
1 \N World 0
2 1 Top 1
3 1 Left 2
4 1 Support 0
5 2 Home 0
6 2 News 1
7 4 Contact 0
8 4 Search 1
9 4 Sitemap 2
10 4 Imprint 3
\.
SELECT pg_catalog.setval ('nav_id_nav_seq', 10, true);
-------------------------8<---------------------------
If I join the resulting tree with another table, the order could be
destroyed again (?)
So I think there should also be a temporary SEQ that can be used in the
ORDER BY clause at the end.
Any idea ?
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
Nabil Sayegh wrote:
> If I join the resulting tree with another table, the order could be
> destroyed again (?)
> So I think there should also be a temporary SEQ that can be used in the
> ORDER BY clause at the end.
>
The only way to do what you want (if I understand correctly) currently
is to use padded keys and then sort by branch. Here's a bit of a
workaround that might do the trick for you:
create or replace function pad_id(int,int) returns text as 'select
repeat(''0'', $2 - length($1::text)) || $1' language 'sql';
create view nav_vw as select pad_id(id_nav,4) as id_nav,
pad_id(id2_nav,4) as id2_nav, nav, pos from nav;
select ss.id_nav,ss.id2_nav,ss.level,ss.branch,n.nav,n.pos from nav_vw
n, (select id_nav,id2_nav,level,branch from
connectby('nav_vw','id_nav','id2_nav','0001',0,'~') as (id_nav text,
id2_nav text, level int, branch text)) as ss where n.id_nav = ss.id_nav
order by ss.branch;
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------------+---------+-----
0001 | | 0 | 0001 | World | 0
0002 | 0001 | 1 | 0001~0002 | Top | 1
0005 | 0002 | 2 | 0001~0002~0005 | Home | 0
0006 | 0002 | 2 | 0001~0002~0006 | News | 1
0003 | 0001 | 1 | 0001~0003 | Left | 2
0004 | 0001 | 1 | 0001~0004 | Support | 0
0007 | 0004 | 2 | 0001~0004~0007 | Contact | 0
0008 | 0004 | 2 | 0001~0004~0008 | Search | 1
0009 | 0004 | 2 | 0001~0004~0009 | Sitemap | 2
0010 | 0004 | 2 | 0001~0004~0010 | Imprint | 3
(10 rows)
In 7.4 there may be a slightly better workaround (if a submitted patch
gets accepted). You can convert branch into an array of integers, and
order by that:
select ss.id_nav, ss.id2_nav, ss.level,
string_to_array(ss.branch,'~')::int[] as branch, n.nav,n.pos from nav n,
(select id_nav,id2_nav,level,branch from
connectby('nav','id_nav','id2_nav','1',0,'~') as (id_nav int, id2_nav
int, level int, branch text)) as ss where n.id_nav = ss.id_nav order by
string_to_array(ss.branch,'~')::int[];
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------+---------+-----
1 | | 0 | {1} | World | 0
2 | 1 | 1 | {1,2} | Top | 1
5 | 2 | 2 | {1,2,5} | Home | 0
6 | 2 | 2 | {1,2,6} | News | 1
3 | 1 | 1 | {1,3} | Left | 2
4 | 1 | 1 | {1,4} | Support | 0
7 | 4 | 2 | {1,4,7} | Contact | 0
8 | 4 | 2 | {1,4,8} | Search | 1
9 | 4 | 2 | {1,4,9} | Sitemap | 2
10 | 4 | 2 | {1,4,10} | Imprint | 3
(10 rows)
I'll think more about a row number column though. Maybe for 7.4 (but
then again, times running out and I have a few things in front of this,
so no promises).
Joe
Am Sam, 2003-06-21 um 06.20 schrieb Joe Conway:
> Nabil Sayegh wrote:
> > If I join the resulting tree with another table, the order could be
> > destroyed again (?)
> > So I think there should also be a temporary SEQ that can be used in the
> > ORDER BY clause at the end.
> >
>
> The only way to do what you want (if I understand correctly) currently
> is to use padded keys and then sort by branch. Here's a bit of a
> workaround that might do the trick for you:
>
> create or replace function pad_id(int,int) returns text as 'select
> repeat(''0'', $2 - length($1::text)) || $1' language 'sql';
>
> create view nav_vw as select pad_id(id_nav,4) as id_nav,
> pad_id(id2_nav,4) as id2_nav, nav, pos from nav;
>
> select ss.id_nav,ss.id2_nav,ss.level,ss.branch,n.nav,n.pos from nav_vw
> n, (select id_nav,id2_nav,level,branch from
> connectby('nav_vw','id_nav','id2_nav','0001',0,'~') as (id_nav text,
> id2_nav text, level int, branch text)) as ss where n.id_nav = ss.id_nav
> order by ss.branch;
Thanks for your reply.
I think for the moment I can live with that.
Although sorting by branch means sorting by primary key (which is more
or less random).
Even the array wouldn't be much better, it would just help getting rid
of the need for padding.
I think the best solution would be to:
0) new parameter for connectby() telling the column to ORDER BY
1) create a new column with SERIAL
2) when selecting all children of an element ORDER BY pos
3) order by 1)
For example (if the pos column is harcoded):
The line:
--------------------8<------------------------------------------------
appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s
IS NOT NULL ORDER BY pos",
--------------------8<------------------------------------------------
has to be changed
build_tuplestore_recursively should get a static counter which should be
incremented and stored with each tuple.
Shouldn't that be all ?
thx
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
Nabil Sayegh wrote: > For example (if the pos column is harcoded): > > The line: > --------------------8<------------------------------------------------ > appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s > IS NOT NULL ORDER BY pos", > --------------------8<------------------------------------------------ > has to be changed > > build_tuplestore_recursively should get a static counter which should be > incremented and stored with each tuple. > > Shouldn't that be all ? Sounds like all that's needed for your case. But to be complete, in addition to changing tablefunc.c we'd have to: 1) come up with a new function call signature that makes sense and does not cause backward compatibility problems for other people 2) make needed changes to tablefunc.sql.in 3) adjust the README.tablefunc appropriately 4) adjust the regression test for new functionality 5) be sure we don't break any of the old cases If you want to submit a complete patch, it would be gratefully accepted -- for review at least ;-) Otherwise, I'll see what I can do between now and the 7.4 feature freeze (July 1), but as I said, no promises. Joe
Am Son, 2003-06-22 um 02.09 schrieb Joe Conway: > Otherwise, I'll see what I can do between now and the 7.4 feature freeze > (July 1), but as I said, no promises. No problem. Just one last question: Will this feature be available to 7.3 ? I'm upgrading our servers to 7.3 now, and have enough trouble with some date issues, and sometimes it takes long for new packages to hit debian testing. bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Nabil Sayegh wrote: > Just one last question: > Will this feature be available to 7.3 ? Whenever I do make the changes, I'll try to keep them compatible with 7.3 and post a copy. Shouldn't be too hard -- the current cvs version (i.e. 7.4devel) of contrib/tablefunc still works with 7.3 and is posted here: http://www.joeconway.com/ Joe
Am Son, 2003-06-22 um 02.09 schrieb Joe Conway: > Sounds like all that's needed for your case. But to be complete, in > addition to changing tablefunc.c we'd have to: > 1) come up with a new function call signature that makes sense and does > not cause backward compatibility problems for other people > 2) make needed changes to tablefunc.sql.in > 3) adjust the README.tablefunc appropriately > 4) adjust the regression test for new functionality > 5) be sure we don't break any of the old cases > > If you want to submit a complete patch, it would be gratefully accepted > -- for review at least ;-) Here's the patch, at least for steps 1-3 I don't know anything about regression tests :( However, I included a patch against 7.3.3 bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Attachment
want an auto timestamping on my table.. SQL of create:
create table healthnotes ( "posted" timestamptz not null default
current_timestamp,
"notes" text,
primary key (posted))
was succ. but how do I set it to autotimestamp when adding entries?
tried:
insert into healthnotes values ('my text notes entry my text notes
entry my text notes entry')
but said the value for timestamp was too long
clearly the autotimestamp isn't set up yet
Michael
Am Mit, 2003-06-25 um 20.17 schrieb Michael Hanna:
> want an auto timestamping on my table.. SQL of create:
> create table healthnotes ( "posted" timestamptz not null default
> current_timestamp,
> "notes" text,
> primary key (posted))
>
> was succ. but how do I set it to autotimestamp when adding entries?
>
> tried:
>
> insert into healthnotes values ('my text notes entry my text notes
> entry my text notes entry')
>
> but said the value for timestamp was too long
> clearly the autotimestamp isn't set up yet
insert into healthnotes (notes) values ('my text notes entry my text
notes entry my text notes entry');
HTH
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
Hi, it's me again.
I thought about that branch as array thing.
What would yout think about a function that ascends a tree from a given
node to the topmost and returns the path as a table ?
Implementing the array n connectby is fine, but as I don't like arrays
too much :) maybe there should be a dedicated reverselookup, too.
If someone is only interested in the path to 1 node, this function could
be handier and possibly faster.
Am Sam, 2003-06-21 um 06.20 schrieb Joe Conway:
> In 7.4 there may be a slightly better workaround (if a submitted patch
> gets accepted). You can convert branch into an array of integers, and
> order by that:
>
> select ss.id_nav, ss.id2_nav, ss.level,
> string_to_array(ss.branch,'~')::int[] as branch, n.nav,n.pos from nav n,
> (select id_nav,id2_nav,level,branch from
> connectby('nav','id_nav','id2_nav','1',0,'~') as (id_nav int, id2_nav
> int, level int, branch text)) as ss where n.id_nav = ss.id_nav order by
> string_to_array(ss.branch,'~')::int[];
> id_nav | id2_nav | level | branch | nav | pos
> --------+---------+-------+----------+---------+-----
> 1 | | 0 | {1} | World | 0
> 2 | 1 | 1 | {1,2} | Top | 1
> 5 | 2 | 2 | {1,2,5} | Home | 0
> 6 | 2 | 2 | {1,2,6} | News | 1
> 3 | 1 | 1 | {1,3} | Left | 2
> 4 | 1 | 1 | {1,4} | Support | 0
> 7 | 4 | 2 | {1,4,7} | Contact | 0
> 8 | 4 | 2 | {1,4,8} | Search | 1
> 9 | 4 | 2 | {1,4,9} | Sitemap | 2
> 10 | 4 | 2 | {1,4,10} | Imprint | 3
> (10 rows)
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
Nabil Sayegh wrote: > What would yout think about a function that ascends a tree from a given > node to the topmost and returns the path as a table ? > Not sure I understand what you're proposing correctly, but can't you just reverse the parent/child id fields? Joe
Am Sam, 2003-06-28 um 01.26 schrieb Joe Conway: > Nabil Sayegh wrote: > > What would yout think about a function that ascends a tree from a given > > node to the topmost and returns the path as a table ? > > > > Not sure I understand what you're proposing correctly, but can't you > just reverse the parent/child id fields? Hm, Eh, yup, that helped :) thx P.S: That should go into the README -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Hi! I know I'm butting in and I'll admit I can't follow exactly what the discussion has been, but it seems (to me at least) to be related to an article I recently read (following a link on Mark Pilgrim's site diveintomark.org: Storing Hierarchical Data in a Database by Gijs Van Tulder <http://www.sitepoint.com/article/1105/1> The further readings suggested at the end of the article were helpful to me as well. If it's not helpful, sorry for the interruption! I'll go back to lurking now. Michael Glaesemann grzm myrealbox com On Saturday, Jun 28, 2003, at 08:40 Asia/Tokyo, Nabil Sayegh wrote: > Am Sam, 2003-06-28 um 01.26 schrieb Joe Conway: >> Nabil Sayegh wrote: >>> What would yout think about a function that ascends a tree from a >>> given >>> node to the topmost and returns the path as a table ? >>> >> >> Not sure I understand what you're proposing correctly, but can't you >> just reverse the parent/child id fields? > > Hm, Eh, yup, that helped :) > > thx > > P.S: That should go into the README > -- > e-Trolley Sayegh & John, Nabil Sayegh > Tel.: 0700 etrolley /// 0700 38765539 > Fax.: +49 69 8299381-8 > PGP : http://www.e-trolley.de > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > >