From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Axel Straschil <axel(at)straschil(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY with LTREE |
Date: | 2006-02-21 06:31:33 |
Message-ID: | 20060221063133.GA84642@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Axel Straschil | 2006-02-21 07:03:41 | Re: ORDER BY with LTREE |
Previous Message | Pedro B. | 2006-02-20 20:56:25 | Re: ... more than one count with left join |