Subject: Re: Recursive SETOF function
From: Richard Rowell <richard@bowmansystems.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <b918cf3d04112208564c5fb783@mail.gmail.com>
References: <1101136693.18148.66.camel@richard>
	<b918cf3d041122085437cde2f1@mail.gmail.com>
	<b918cf3d04112208564c5fb783@mail.gmail.com>
Content-Type: text/plain
Date: Mon, 22 Nov 2004 13:25:52 -0600
Message-Id: <1101151552.18145.86.camel@richard>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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 on why the RETURN NEXT doesn't like the variable as
a parameter?

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