In order to simplify the regular expressions, I propose to change the
"." as "field separator" in the ids: "." has a special meaning in
regular expressions, let us avoid escaping all over the place. So,
take for instance "/" as separator, so that the message in my previous
example is now "25/7/19/2".
In order to compute the correct id at insert time, I would suggest
keeping a sequence root_seq for the root messages (see CREATE SEQUENCE
in the postgres manual), and just "select nextval(root_seq)"
each time you insert a new root message. Alternatively, if you write
root message ids as e.g. "/25" and keep the previous structure, you
can use the method described below also for root messages. In this
case, you just would be interpreting the root messages as "replies to
the (fictitious) message with an empty index".
Now assume you want to insert a new reply to message with id X (which
could be at any level, e.g. X = 25/7/19). You can get the number of
the next response to X = 25/7/19 using the regular expression
capabilities of postgres "select count(id)+1 as Y from your_table where id ~ '25/7/19/[^/]*$' "
and then compute the index to be inserted as "X/Y"
In the regexp you are requesting something that matches 25/7/19/(any number of symbols different from "/")
so that all direct replies are selected, but NOT the replies to them -
as they would have a "/" somewhere before the end.
You can automatize this with the sql functions create function next_reply_num(text) returns int4 as 'select
count(*)+1from ids where id ~ ($1|| ''/[^/]*$'') ' language 'sql';
create function next_reply_id(text) returns text as 'select ($1 || ''/'' || next_reply_num($1)::text)'
language'sql';
You could then insert the next reply to message "25/19/2" using
insert into messages(id, ...) values(next_reply_id('25/19/2'), ...);
I do not know how to do this within a single call to an sql function;
it would be easy to do if you use either PL/tcl or PL/pgsql procedural
languages.
Thanks for the "challenge": this IS fun.
Miguel