Date: Mon, 20 Feb 2006 23:31:33 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Axel Straschil <axel@straschil.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY with LTREE
Message-ID: <20060221063133.GA84642@winnie.fuhr.org>
References: <slrndvbnbs.l6a.axel@m2.sine>
	<20060220074120.GA31596@winnie.fuhr.org>
	<Pine.LNX.4.64.0602201105330.2245@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.LNX.4.64.0602201105330.2245@localhost>

On Mon, Feb 20, 2006 at 11:15:43AM +0100, Axel Straschil wrote:
> I have a table with a ltree and acaption "CREATE TABLE (tree LTREE, 
> caption TEXT)", I can't store the data of caption in the ltree, becouse of 
> the limitation of the ltree labels. I use the ltree to give the captions 
> a direcory like hirachy, wenn a new caption is createt, the customer can 
> decide which is the parent caption.
> 
> My Problem now ist, that I want to show in a list-box all the captions 
> ordered by ltree, but in the same hirachy by the caption.

Maybe somebody can think of a better way, but as I mentioned in my
previous post you could create a custom operator to use in the ORDER
BY clause.  The operator's function would receive two rows: if the
rows have a common parent in the hierarchy then the function could
compare the rows' labels; otherwise it could query for other rows
and perform appropriate comparisons.  With a large table those queries
could make ordering expensive; if anybody has a better suggestion
then hopefully they'll mention it.

Below is a simple example using the data from your original message
and giving the results you requested.  It's intended to be a starting
point, not a final solution.  I have only a little experience with
ltree and with using custom operators in this manner, so corrections
and improvements are welcome.

CREATE TABLE foo (tree ltree, caption text);

INSERT INTO foo VALUES ('root.1', 'z');
INSERT INTO foo VALUES ('root.2', 'c');
INSERT INTO foo VALUES ('root.2.1', 'a');
INSERT INTO foo VALUES ('root.2.2', 'b');
INSERT INTO foo VALUES ('root.3', 'i');
INSERT INTO foo VALUES ('root.4', 'f');
INSERT INTO foo VALUES ('root.4.1', 'k');
INSERT INTO foo VALUES ('root.4.2', 'c');

CREATE FUNCTION foo_lt(foo, foo) RETURNS boolean AS $$
DECLARE
    level  integer;
    row1   foo%ROWTYPE;
    row2   foo%ROWTYPE;
BEGIN
    IF subpath($1.tree, 0, -1) = subpath($2.tree, 0, -1) THEN
        RETURN $1.caption < $2.caption;
    ELSIF $1.tree @> $2.tree THEN
        RETURN true;
    ELSE
        level := nlevel(lca($1.tree, $2.tree)) + 1;
        SELECT INTO row1 * FROM foo WHERE tree = subpath($1.tree, 0, level);
        SELECT INTO row2 * FROM foo WHERE tree = subpath($2.tree, 0, level);
        RETURN row1.caption < row2.caption;
    END IF;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

CREATE OPERATOR < (
    PROCEDURE = foo_lt,
    LEFTARG   = foo,
    RIGHTARG  = foo
);

SELECT * FROM foo ORDER BY foo USING <;
   tree   | caption 
----------+---------
 root.2   | c
 root.2.1 | a
 root.2.2 | b
 root.4   | f
 root.4.2 | c
 root.4.1 | k
 root.3   | i
 root.1   | z
(8 rows)

-- 
Michael Fuhr