Re: [GENERAL] Tree structure

On Fri, 26 Feb 1999, Kaare Rasmussen wrote:

> I can't figure this one out. I need a tree structure like this
>
> Number Pointer
> 1 0
> 2 1
> 3 1
> 4 2
> 5 0
> 6 1
> 7 5
>
> This should somehow show up like this
> Number
> 1
> 2
> 4
> 3
> 6
> 5
> 7
>
> The whole excercise is because I'd like to show a tree structure:
>
> 1
> - 2
> - - 4
> - 3
> - 6
> 5
> - 7
>
> Is this possible with PostgreSQL?

This does the trick:

select textcat(text_substr('--------- '::text,10-pointer,pointer+1),int4_text(num)) as ptr from a order by num;

It produces the output:

test=> select textcat(text_substr('--------- '::text,10-pointer,pointer+1), int4_text(num)) as ptr from a order by num;
ptr
-------
1
- 2
- 3
-- 4
5
- 6
----- 7
(7 rows)

Turning off headings and alignment:

test=> \a
turned off field alignment
test=> \t
turned off output headings and row count
test=> select textcat(text_substr('--------- '::text,10-pointer,pointer+1), int4_text(num)) as ptr from a order by num;
1
- 2
- 3
-- 4
5
- 6
----- 7

Peter

--
Peter T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf