recursive call to function returning SETOF - Mailing list pgsql-novice

Hello all,

I�m attempting to write a function in plpgsql (for PostgreSQL 7.3.4) to
power a threaded discussion forum, and am having difficulties calling the
function in question recursively.  The function returns a SETOF (see the
function definition below) a custom type I created to hold a row of data.
I�d be grateful if anyone could share their experiences calling functions
returning a SETOF recursively.

--Max

Here�s the table:
-- simple table for forum posts
CREATE TABLE "public"."post" (
  post_id SERIAL,
  "subject" VARCHAR(75) NOT NULL,
  "parent_post_id" INTEGER,
  "has_children" BOOLEAN DEFAULT FALSE, -- does this post have child-posts?
  "date_posted" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  "message" VARCHAR(5000),
  "level" INTEGER DEFAULT 0,  -- level of post
  PRIMARY KEY("post_id"),
  -- recusive relationship for parent-child messages
  FOREIGN KEY ("parent_post_id") REFERENCES "public"."post"("post_id")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
  NOT DEFERRABLE
) WITH OIDS;



This is the type to hold the post:
-- type for 'post'
CREATE TYPE "public"."t_post" AS (
  "post_id" INTEGER,
  "subject" VARCHAR(75),
  "parent_post_id" INTEGER,
  "has_children" BOOLEAN,
  "date_posted" TIMESTAMP(0) WITHOUT TIME ZONE,
  "message" VARCHAR(5000),
  "level" INTEGER
);



This is the function:
-- this function is initially called with the param '0', to grab all top
level messages
CREATE FUNCTION "public"."get_posts" (integer) RETURNS SETOF t_post AS '
DECLARE
    post t_post%rowtype;
    input_post_id ALIAS FOR $1;  -- for grabbing child messages,
BEGIN
RAISE NOTICE ''debug: input_post_id = %'',input_post_id;
-- if input_post_id was not specified, get top level messages
IF input_post_id = 0 THEN
    FOR post IN
        SELECT
            p.post_id,
            p.subject,
            p.parent_post_id,
            p.has_children,
            p.date_posted,
            p.message,
            p.level
        FROM post p
        WHERE p.parent_post_id IS NULL
    LOOP
        return NEXT post;
        -- now check if post.post_id has children, if so call recursively

        IF post.has_children = TRUE THEN
            RAISE NOTICE ''debug: post has children, calling recursively, post_id =
%'',post.post_id;
            -- call recursively
            get_posts(post.post_id);
        ELSE
            RAISE NOTICE ''debug: post lacks children, post_id = %'',post.post_id;
        END IF;

    END LOOP;
    RETURN;
-- if a input_post_id was passed get the child messages of that post_id
ELSE
    FOR post IN
        SELECT
            p.post_id,
            p.subject,
            p.parent_post_id,
            p.has_children,
            p.date_posted,
            p.message,
            p.level
        FROM post p
        WHERE p.parent_post_id = input_post_id
    LOOP
        return NEXT post;
        -- now check if post.post_id has children, if so call recursively

        IF post.has_children = TRUE THEN
            RAISE NOTICE ''debug: post has children, calling recursively, post_id =
%'',post.post_id;
            -- call recursively
            get_posts(post.post_id);
        ELSE
            RAISE NOTICE ''debug: post lacks children, post_id = %'',post.post_id;
        END IF;

    END LOOP;
    RETURN;
END IF;

END
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

_________________________________________________________________
Express yourself with MSN Messenger 6.0 -- download now!
http://www.msnmessenger-download.com/tracking/reach_general