Re: connectby(... pos_of_sibling)

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