another question about connectby from contrib

From: sector119(at)mail(dot)ru
To: pgsql-general(at)postgresql(dot)org
Subject: another question about connectby from contrib
Date: 2003-04-23 12:03:36
Message-ID: 20030423120336.GA19733@city.gov.te.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

ELECT f.*, link, text, target, icon, node FROM connectby('menu', 'id',
'parent_id', 8, 0) AS f(id integer, parent_id integer, level integer),
menu where menu.id = f.id;

I have got this:

id | parent_id | level | link | text | target | icon | node
----+-----------+-------+------+-----------------+--------+------+------
8 | | 0 | | #3 | | | t
11 | 8 | 1 | | #3.1 | | | t
12 | 8 | 1 | | #3.2 | | | f
13 | 11 | 2 | | #3.1.1 | | | f
14 | 11 | 2 | | #3.1.2 | | | t
15 | 11 | 2 | | #3.1.3 | | | f
16 | 11 | 2 | | #3.1.4 | | | t
17 | 11 | 2 | | #3.1.5 | | | f
18 | 14 | 3 | | #3.1.2.1 | | | f
19 | 14 | 3 | | #3.1.2.2 | | | f
20 | 14 | 3 | | #3.1.2.3 | | | f
21 | 16 | 3 | | #3.1.4.1 | | | f
22 | 16 | 3 | | #3.1.4.2 | | | f
23 | 16 | 3 | | #3.1.4.3 | | | t
24 | 16 | 3 | | #3.1.4.4 | | | f
25 | 23 | 4 | | #3.1.4.3.1 | | | f
26 | 23 | 4 | | #3.1.4.3.2 | | | f
27 | 23 | 4 | | #3.1.4.3.3 | | | f
28 | 16 | 3 | | #3.1.4.5 | | | f

How am I able to get result where text field is like this:

#3
#3.1
#3.1.1
#3.1.2
#3.1.2.1
#3.1.2.2
#3.1.2.3
#3.1.3
#3.1.4
#3.1.4.1
#3.1.4.2
#3.1.4.3
#3.1.4.3.1
#3.1.4.3.2
#3.1.4.3.3
#3.1.4.4
#3.1.4.5
#3.1.5
#3.2

--
WBR, sector119

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-04-23 13:41:27 Re: Left Join Not Using Index?
Previous Message Richard Huxton 2003-04-23 11:27:38 Re: Can I Benefit from and Index Here?