From: mig@utdt.edu
To: BIngram@sixtyfootspider.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Threaded Records in SQL: Advice Needed
Date: Tue, 11 Apr 2000 10:01:41 -0300
Message-ID: <200004111301.KAA04612@ant.utdt> (raw)
In-Reply-To: <9B7D4396307CD311809A00500415EB405D2205@BKMAIL>
References: <9B7D4396307CD311809A00500415EB405D2205@BKMAIL>
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(*)+1 from 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mig@utdt.edu, BIngram@sixtyfootspider.com
Subject: Re: Threaded Records in SQL: Advice Needed
In-Reply-To: <200004111301.KAA04612@ant.utdt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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