Postgres Pro
Downloads
Home   >   mailing lists

Thread: connectby usage question

connectby usage question

From
Eric E
Date:
Hi all,
    We're trying to use connectby to transform a tree into an ordered set
of rows, specifically requirements rows.

We have data stored in a table with
keyid and parent_id, as described in the examples for

keyid is also the primary key of a table A, which has the field
A.seq_number.

We can successfully sort the output of connectby using seqnumber.
connectby does return as branch the concatenation of the keyids from
root to leaf for each branch.

However, we would like to return a numbering the items based on the
order they return, like so;

1 (first item)
1.1 (1 branch, 1st item)
1.2 (1 branch, 2nd item)
1.2.1 (1.2 branch, 1st item)
1.2.2
1.2.2.1
1.3
2
2.1
2.1.1
2.1.2
2.1.3
2.2
2.3

etc.
Is there an option in connectby to do this directly, or do we need to
write another function to do this?

Thanks,

Eric

Re: connectby usage question

From
Joe Conway
Date:
Eric E wrote:
>
> However, we would like to return a numbering the items based on the
> order they return, like so;
>
> 1 (first item)
> 1.1 (1 branch, 1st item)
> 1.2 (1 branch, 2nd item)
> 1.2.1 (1.2 branch, 1st item)
> 1.2.2
> 1.2.2.1
> 1.3

> etc.
> Is there an option in connectby to do this directly, or do we need to
> write another function to do this?

There is no support for this in connectby, but I imagine it wouldn't be
too hard to hack connectby to do it for you. Probably much easier to do
that than try to add it with another function.

Joe

Re: connectby usage question

From
Jim Nasby
Date:
contrib/ltree might be of use to you.

On Nov 14, 2006, at 3:46 PM, Eric E wrote:
>     We're trying to use connectby to transform a tree into an ordered
> set of rows, specifically requirements rows.
>
> We have data stored in a table with
> keyid and parent_id, as described in the examples for
>
> keyid is also the primary key of a table A, which has the field
> A.seq_number.
>
> We can successfully sort the output of connectby using seqnumber.
> connectby does return as branch the concatenation of the keyids
> from root to leaf for each branch.
>
> However, we would like to return a numbering the items based on the
> order they return, like so;
>
> 1 (first item)
> 1.1 (1 branch, 1st item)
> 1.2 (1 branch, 2nd item)
> 1.2.1 (1.2 branch, 1st item)
> 1.2.2
> 1.2.2.1
> 1.3
> 2
> 2.1
> 2.1.1
> 2.1.2
> 2.1.3
> 2.2
> 2.3
>
> etc.
> Is there an option in connectby to do this directly, or do we need
> to write another function to do this?
>
> Thanks,
>
> Eric
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)