From: Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
To: pgsql-sql@postgresql.org
Subject: Re: SQL a simple menu - plz help
Date: Sun, 23 Nov 2003 16:51:14 +0000 (UTC)
Message-ID: <Xns943C5804EB151rr8xca@200.46.204.72> (raw)
References: <3fc07554$0$171$edfadb0f@dtext02.news.tele.dk>
> As subj says. A simple menu is defined in a table. But how to extract
> it? Heres the deal:
>
> Table menu has these 3 columns: menuid parent menudesc
>
> Ok this should be easy right? Those menu-rows that has parent=0 is
> "toplevel". And so forth. An example:
>
> 1 0 "Items"
> 2 0 "Standings"
> 3 0 "Test"
> 4 2 "Liverpool"
> 5 2 "AC Milan"
> 6 1 "Itemno1"
>
> This menu should in layout come in this order:
>
> Items
> Itemno1
> Test
> Standings
> AC Milan
> Liverpool
>
> That is: Alphabetically sorted and with the children underneath the
> parents. Right? Yes - and selecting the menu and doing some gymnastics
> in ASP isnt that hard. But ASP must be the language that has the
> "crapiest" way of handling arrays...so a simple task is really messy.
> Creating a stored procedure involes 2-3 temporary tables as I see it.
> Not vey elegant either.
>
> So my question is: Isnt there any way of selecting this order directly
> using SQL?? Some nice clean and beautiful way?
You're in need of the "CONNECT BY" option which is ideal for scenarios
such as yours. Unfortunately PostgreSQL doesn't have it yet, but I believe
there is a PLSQL script (or something like this) which emulates the CONNECT
BY behaviour and it's called "connectby()" or something like that.
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rr@8x.ca
Subject: Re: SQL a simple menu - plz help
In-Reply-To: <Xns943C5804EB151rr8xca@200.46.204.72>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox