pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY with LTREE
4+ messages / 2 participants
[nested] [flat]
* ORDER BY with LTREE
@ 2006-02-17 14:28 Axel Straschil <axel@straschil.com>
2006-02-20 07:41 ` Re: ORDER BY with LTREE Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Axel Straschil @ 2006-02-17 14:28 UTC (permalink / raw)
To: pgsql-sql
Hello!
Im working with the ltree [1] datatype and have labels that can not used
directly in ltree and a want to get a tree like strukture ordered by the
labels.
IE, I've got a table
CREATE TABLE t
(
tree LTREE,
label TEXT
);
and data like
tree label
--------------------------
root.1 z
root.2 c
root.2.1 a
root.2.2 b
root.3 i
root.4 f
root.4.1 k
root.4.2 c
I need a VIEW that ordery by "by tree but by label in the same hirachie",
so the output should be
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
Any idea?
Thanks,
AXEL.
[1] http://www.sai.msu.su/~megera/postgres/gist/ltree/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ORDER BY with LTREE
2006-02-17 14:28 ORDER BY with LTREE Axel Straschil <axel@straschil.com>
@ 2006-02-20 07:41 ` Michael Fuhr <mike@fuhr.org>
2006-02-21 06:31 ` Re: ORDER BY with LTREE Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2006-02-20 07:41 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; +Cc: pgsql-sql
On Fri, Feb 17, 2006 at 02:28:13PM +0000, Axel Straschil wrote:
> Im working with the ltree [1] datatype and have labels that can not used
> directly in ltree and a want to get a tree like strukture ordered by the
> labels.
Are you saying that you can't convert labels into ltree values like
'c.a' and order by that? What data type are the real labels?
One possibility would be to create a custom operator and add a USING
clause to the ORDER BY clause. The operator could compare records
by whatever algorithm you like, even if that means looking up other
records (e.g., to determine an ancestor's label). That could be
expensive for a large table but it might be worth considering.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ORDER BY with LTREE
2006-02-17 14:28 ORDER BY with LTREE Axel Straschil <axel@straschil.com>
2006-02-20 07:41 ` Re: ORDER BY with LTREE Michael Fuhr <mike@fuhr.org>
@ 2006-02-21 06:31 ` Michael Fuhr <mike@fuhr.org>
2006-02-21 07:03 ` Re: ORDER BY with LTREE Axel Straschil <axel@straschil.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2006-02-21 06:31 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ORDER BY with LTREE
2006-02-17 14:28 ORDER BY with LTREE Axel Straschil <axel@straschil.com>
2006-02-20 07:41 ` Re: ORDER BY with LTREE Michael Fuhr <mike@fuhr.org>
2006-02-21 06:31 ` Re: ORDER BY with LTREE Michael Fuhr <mike@fuhr.org>
@ 2006-02-21 07:03 ` Axel Straschil <axel@straschil.com>
0 siblings, 0 replies; 4+ messages in thread
From: Axel Straschil @ 2006-02-21 07:03 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Hello Michael!
> Below is a simple example using the data from your original message
Thanks for the example, that realy helps me!
Lg,
AXEL.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-21 07:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-17 14:28 ORDER BY with LTREE Axel Straschil <axel@straschil.com>
2006-02-20 07:41 ` Michael Fuhr <mike@fuhr.org>
2006-02-21 06:31 ` Michael Fuhr <mike@fuhr.org>
2006-02-21 07:03 ` Axel Straschil <axel@straschil.com>
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