Re: connectby usage question

From: Jim Nasby <jim(at)nasby(dot)net>
To: Eric E <whalesuit(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: connectby usage question
Date: 2006-11-15 15:46:07
Message-ID: EE3BD115-5C24-4FDF-B3C1-0DDDC7B4DA9B@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2006-11-15 15:47:49 Re: Using SAN Splits to instantly copy a DB
Previous Message Gurjeet Singh 2006-11-15 15:32:33 Re: Question about query optimization