Hi all,
I need to use the tree_sortkey functionallity in one table referencing a parent_id in the same table, and I read about what is already done in the forums package.
However I don't understand the purpose of the max_child_sortkey field (which is in the forums_messages table and in the acs_objects table too), because as far as I could noticed, it is only used to calculate the next sortkey of a newly created node (the nex new sortkey among its already created sister nodes), but if that's its only purpose, why can not calculate the next sorkey directly?
I did it this way and it's working fine, but since I couldn't find any documentation (about the max_child_sortkey), I want to know what you have to say about it.
This is my implementation:
create table table_name (
primary_key_id integer primary key,
parent_id integer references table_name,
sortkey varbit
);create or replace function table_name_insert_fn () returns opaque as '
declare
v_new_sortkey varbit;
v_parent_sortkey varbit;
begin
if new.parent_id is null
then
v_new_sortkey := tree_increment_key('''');
else
select sortkey into v_parent_sortkey from table_name where primary_key_id = new.parent_id;
if (select count(*) from table_name where parent_id = new.parent_id) = 0
then
v_new_sortkey := v_parent_sortkey || tree_increment_key('''');
else
select tree_increment_key(sortkey) into v_new_sortkey
from table_name
where tree_leaf_key_to_int(sortkey) = (select max(tree_leaf_key_to_int(sortkey))
from table_name
where parent_id = new.parent_id);
v_new_sortkey := v_parent_sortkey || v_new_sortkey;
end if;
end if;
raise NOTICE ''veamos % '', v_new_sortkey;
new.sortkey = v_new_sortkey;
return new;
end;' language 'plpgsql';
create trigger table_name_insert_tr
before insert on table_name
for each row
execute procedure table_name_insert_fn();
insert into table_name(primary_key_id) values (100);
insert into table_name(primary_key_id,parent_id) values (101,100);
insert into table_name(primary_key_id,parent_id) values (102,100);
insert into table_name(primary_key_id,parent_id) values (103,101);
insert into table_name(primary_key_id,parent_id) values (104,102);
insert into table_name(primary_key_id,parent_id) values (105,100);
insert into table_name(primary_key_id,parent_id) values (106,101);
insert into table_name(primary_key_id,parent_id) values (107,100);
insert into table_name(primary_key_id,parent_id) values (108,102);
insert into table_name(primary_key_id,parent_id) values (109,103);
insert into table_name(primary_key_id,parent_id) values (110,103);
select * from table_name;
primary_key_id | parent_id | sortkey | max_child_sortkey
----------------+-----------+----------------------------------+-------------------
100 | | 00000001 |
101 | 100 | 0000000100000001 |
102 | 100 | 0000000100000010 |
103 | 101 | 000000010000000100000001 |
104 | 102 | 000000010000001000000001 |
105 | 100 | 0000000100000011 |
106 | 101 | 000000010000000100000010 |
107 | 100 | 0000000100000100 |
108 | 102 | 000000010000001000000010 |
109 | 103 | 00000001000000010000000100000001 |
110 | 103 | 00000001000000010000000100000010 |
Is this right?
TIA
Jose Pablo
