DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=er/iLyG3l9Te6Tv9qC9fyS6q+iFNAQxfzps4RaeKqKJzW6ys7pZwYGUupXd27ThGdvU24zMan0jlcjClX2gYTMR+59v/NNhN7StBF7NTuqEXrf4tHdY6Do9XsR9E6n0jU3s9qXdSlRFt4B+4Qws+oUWCUd8/b54pA6uiaV7iY84=
Message-ID: <b918cf3d0411221139505a15ce@mail.gmail.com>
Date: Mon, 22 Nov 2004 14:39:21 -0500
From: Mike Rylander <mrylander@gmail.com>
Reply-To: Mike Rylander <mrylander@gmail.com>
To: Richard Rowell <richard@bowmansystems.com>
Subject: Re: [postgres] Re: Recursive SETOF function
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1101149501.18148.81.camel@richard>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <1101136693.18148.66.camel@richard>
	<b918cf3d041122085437cde2f1@mail.gmail.com>
	<b918cf3d04112208564c5fb783@mail.gmail.com>
	<1101149501.18148.81.camel@richard>

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
> 
> 


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