NYCPHP Meetup

[Steve Manes]

Kenneth Downs wrote:
> The bit of SQL you need to make it child's play is the "WITH RECURSE" 
> feature, which is sadly not widely supported:
> 
> SELECT record_id, text_of_message
>    FROM messages  chd
>    JOIN messages par  ON chd.record_id_par = par.record_id WITH RECURSE
>   WHERE chd.record_id = $x

The Oracle alternative is CONNECT BY/START WITH.  PostgreSQL has a 
similar package in contribs/tablefunc.

Here's a PostgreSQL stored procedure I used to dump a pre-order twalk 
trace of a tree-structured message base.

------------------------------------------------------------------------------
--  TRACE_MESSAGE 
     --
--  Return the ordered thread below msgnum 
      --
------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trace_message (REFCURSOR, TEXT, INTEGER, 
INTEGER) RETURNS REFCURSOR AS $$
   DECLARE
     v_curmsg            ALIAS FOR $2;
     v_who_id            ALIAS FOR $3;
     v_trace_depth       ALIAS FOR $4;
   BEGIN
     OPEN $1 FOR
     SELECT
         TREE.*,
         who.user_name AS author,
         message.subject,
         message.author_id,
         message.forum_id,
         message.is_invisible,
         message.is_one_liner,
         message.is_forum_header
     FROM
         connectby('tree', 'message_id', 'parent_id', 'sibling_order', 
v_curmsg, v_trace_depth) AS TREE (message_id INT, parent_id INT, level 
INT, sibling_order INT)
         LEFT JOIN message ON message.message_id = TREE.message_id
         LEFT JOIN who ON who.who_id = message.author_id
     WHERE
	-- various froo-froo filters
     ORDER BY TREE.sibling_order;

     RETURN $1;
   END;

$$ LANGUAGE 'plpgsql';