fsckd

PostgreSQL Recursion

19.3.2009

Here is that example code I promised about SQL recursion. Basically, blog_comment's have a parent_comment_id field that refers to the id of its parent comment. You would call the function below with something like:

SELECT * FROM get_comments(1);

- which would return the children comments of the blog_comment with id 1. I know the code can probably be cleaned up quite a bit, so understand this was my first time writing SQL logic while you're reading it.

CREATE FUNCTION get_comments(integer) RETURNS SETOF blog_comment AS $_$DECLARE
    tmp blog_comment%ROWTYPE;
    tmp2 blog_comment%ROWTYPE;
    BEGIN
        FOR tmp IN SELECT * FROM ...

4 comments

• PostgreSQL

PostgreSQL Recursion

15.3.2009

This isn't really covered anywhere, but PostgreSQL has a built-in language that makes a comment system really easy. You can recursively find the comment hierarchy, which will give you a Reddit-style comment layout. This ends up being much simpler than flat solutions or depth tracking.

Edit: example code here.

no comments

• PostgreSQL