Skip site navigation (1) Skip section navigation (2)

Re: [postgres] Re: Recursive SETOF function

From: Richard Rowell <richard(at)bowmansystems(dot)com>
To: Mike Rylander <mrylander(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [postgres] Re: Recursive SETOF function
Date: 2004-11-22 20:21:00
Message-ID: 1101154860.18145.93.camel@richard (view raw or flat)
Thread:
Lists: pgsql-sql
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
> > 
> > 
> 
> 
-- 


In response to

pgsql-sql by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-11-22 23:19:17
Subject: Re: Recursive SETOF function
Previous:From: Mike RylanderDate: 2004-11-22 19:39:21
Subject: Re: [postgres] Re: Recursive SETOF function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group