Re: Best way to store a threaded message list/tree in SQL - Mailing list pgsql-general

Mike,
connectby() is your friend here.  Do a search on tablefunc in the help file.
> CREATE Table wallposts
> (
>  id uuid NOT NULL,
>  posted timestamp NOT NULL,
>  userid uuid NOT NULL,
>  posterid uuid NOT NULL,
>  parentid uuid NOT NULL,
>  comment text NOT NULL
> )
SELECT * FROM connectby('wallposts', 'id', 'parentid', '<entrypoint>',
0, '~')
  AS t(keyid text, parent_keyid text, level int, branch text);

HTH,

Johan Nel
Durban, South Africa.