tree ordering with varbit

From: Scott Lamb <slamb(at)slamb(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: tree ordering with varbit
Date: 2002-11-18 01:30:42
Message-ID: 3DD842C2.3080508@slamb.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Got a couple of questions. Short version:

- Are there conversion functions from integer and timestamp to bit varying?

- Would sorting a timestamp by its "bit varying" value be equivalent to
sorting by the timestamp itself?

The long version, which includes why I'm asking, is below:

I saw something in the OpenACS code about using the bit varying type to
order trees. I think it worked something like this. Given a structure
like this:

create table mb.message (
message_id serial primary key,
messageroot_id integer not null references mb.messageroot,
parent_id integer references mb.message (message_id),
...
);

all of the messages with the same messageroot make a forest. If I wanted
to sort them hierarchically based when they were posted, I'd want a sort
key that has their post time prefixed by that of all their ancestors, so
the greatest ancestor comes first. Or better yet, their IDs, since
that's unique and means children of two parents that happened to be
posted at the same time wouldn't be lumped together, and IDs should
increase as posting times increase.

So I need a type that can expand. An array or a varying-size type.
Arrays might work for the above, but if I want to sort by a couple of
different types, then I'm screwed. varbit already sorts in the right way
for integer, at least. So I need conversion functions and hopefully to
know that it sorts right for timestamps also.

Thanks,
Scott

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew V. 2002-11-18 02:27:41 Re: DECLARE CURSOR
Previous Message Tom Lane 2002-11-18 00:30:31 Re: [GENERAL] DECLARE CURSOR