Re: [postgres] Re: Recursive SETOF function

I had to fiddle a bit more, but I did finally get it to work. Thanks
Mike

CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER)
RETURNS SETOF INTEGER
AS '
DECLARE
child_provider ALIAS FOR $1;
cid INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
IF cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
RETURN;
END IF;
cid := child_provider;
LOOP
EXIT WHEN cid IS NULL;
RAISE WARNING ''LOOP: Adding (%) to results'', cid;
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE uid=cid;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';

On Mon, 2004-11-22 at 14:39 -0500, Mike Rylander wrote:
> Sorry about that... try this:
>
> CREATE OR REPLACE FUNCTION svp_getparentproviderids
> (INTEGER)
> RETURNS SETOF svp_getparentproviderids_uid_type
> AS '
> DECLARE
> child_provider ALIAS FOR $1;
> cid svp_getparentproviderids_uid_type%ROWTYPE;
> tmp_cid INTEGER;
> BEGIN
> SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider;
> IF tmp_cid = 0 THEN
> RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
> RETURN;
> END IF;
> cid.uid := child_provider;
> LOOP
> EXIT WHEN tmp_cid IS NULL;
> RETURN NEXT cid;
> SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid;
> END LOOP;
> RETURN;
> END;' LANGUAGE 'plpgsql';
>
> On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell
> <richard(at)bowmansystems(dot)com> wrote:
> > I have been fiddling with what you sent. I have it working mostly, save
> > for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I
> > remove this line then the function works ( but returns nothing of
> > course). Any ideas?
> >
> > sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids
> > (INTEGER)
> > RETURNS SETOF svp_getparentproviderids_uid_type
> > AS '
> > DECLARE
> > child_provider ALIAS FOR $1;
> > cid INTEGER;
> > BEGIN
> > SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
> > IF cid = 0 THEN
> > RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
> > RETURN;
> > END IF;
> > cid := child_provider;
> > LOOP
> > EXIT WHEN cid IS NULL;
> > RETURN NEXT cid;
> > SELECT INTO cid parent_id FROM providers WHERE uid=cid;
> > END LOOP;
> > RETURN;
> > END;' LANGUAGE 'plpgsql';
> > CREATE FUNCTION
> > sp_demo_505=# select * from svp_getparentproviderids(21112);
> > ERROR: incorrect argument to RETURN NEXT at or near "cid"
> > CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near
> > line 13
> >
> >
>
>
--