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 blog_comment WHERE parent_comment_id =$1 ORDER BY id ASC LOOP
            RETURN NEXT tmp;
            FOR tmp2 IN SELECT * FROM get_comments(tmp.id) LOOP
                RETURN NEXT tmp2;
            END LOOP;
        END LOOP;
    RETURN;
END;
$_$
    LANGUAGE plpgsql;

Edit: I am very new to SQL functions, so if you have a better solution, feel free to share.

• PostgreSQL

 _   _  __       _      
| | | |/ /_     | | ___ 
| |_| | '_ \ _  | |/ _ \
|  _  | (_) | |_| |  __/
|_| |_|\___/ \___/ \___|
                        

 _   _  __       _      
| | | |/ /_     | | ___ 
| |_| | '_ \ _  | |/ _ \
|  _  | (_) | |_| |  __/
|_| |_|\___/ \___/ \___|
                        

 _   _  __       _      
| | | |/ /_     | | ___ 
| |_| | '_ \ _  | |/ _ \
|  _  | (_) | |_| |  __/
|_| |_|\___/ \___/ \___|
                        

 _   _  __       _      
| | | |/ /_     | | ___ 
| |_| | '_ \ _  | |/ _ \
|  _  | (_) | |_| |  __/
|_| |_|\___/ \___/ \___|
                        

 _   _  __       _      
| | | |/ /_     | | ___ 
| |_| | '_ \ _  | |/ _ \
|  _  | (_) | |_| |  __/
|_| |_|\___/ \___/ \___|