Re: recursive call to function returning SETOF

Hello all,

Im 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.
Id be grateful if anyone could share their experiences calling functions
returning a SETOF recursively.

--Max

Heres 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

On Mon, 15 Sep 2003, Max Nachlinger wrote:

> Hello all,
>
> Im 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.
> Id be grateful if anyone could share their experiences calling functions
> returning a SETOF recursively.

I think you'd need to call it like
FOR record IN SELECT * from get_posts(post.post_id) LOOP
RETURN NEXT record;
END LOOP;