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 '
DECLAREchild_provider ALIAS FOR $1;cid INTEGER;
BEGIN  SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;  IF cid = 0 THEN      RAISE EXCEPTION
''InexistentID --> %'', 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
providersWHERE 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@bowmansystems.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
> > 
> > 
> 
> 
--