Re: [postgres] Re: Recursive SETOF function

Sorry about that... try this:

CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS '
DECLAREchild_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
''InexistentID --> %'', child_provider;      RETURN;  END IF;  cid.uid := child_provider;  LOOP      EXIT WHEN tmp_cid
ISNULL;      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
> 
> 


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer