Re: tree ordering with varbit

Scott Lamb wrote:
> Joe Conway wrote:
>
>> regression=# SELECT * FROM connectby('connectby_int', 'keyid',
>> 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level
>> int, branch text);
>
>
> Yeah, I saw that. But I don't think I can use it for a few reasons:
>
> - I could't see a way to do multi-item sorts, like by score descending
> and then post time ascending, for example.

If I understand what you're saying correctly, I think you can just join back
to the real table from the table function by the primary key. Then you can
sort on anything you want.

>
> - I couldn't see a way to filter for "messageroot_id = ?" first, so it
> would probably be unnecesarily slow as the number of messages in this
> discussion / the number of messages total ratio becomes small.

Do you mean "where messageroot_id = any_value"? Yeah, I couldn't think of a
good, flexible way to do this. The problem is that you can either run the
function for every value in the table (too slow and doesn't make sense
anyway), or you can somehow try to determine which records are root nodes. I
suppose we could allow a NULL be passed in for "start_with" to indicate a
wildcard, and then iterate internally over all records that have "parent_id"
IS NULL. Still won't work for every case, but perhaps better than not having
the option at all? Any other suggestions?

> - Also performance - I can't cache the results as a row in the table,
> which I could see being important at some point. Kind of a pain to keep
> in sync, like all denormalized things, but I could see this being an
> expensive operation.

Why not? Just SELECT the results of the connectby() function (optionally
joined with other tables) into a table (temp or otherwise).


I'm interested in making this function useful for as many cases as possible. I
appreciate the feedback.

Thanks,

Joe