X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF5233A5335 for ; Mon, 22 Nov 2004 20:21:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61775-08 for ; Mon, 22 Nov 2004 20:21:09 +0000 (GMT) Received: from www.bowmansystems.com (ns1.bowmansystems.com [65.166.193.2]) by svr1.postgresql.org (Postfix) with ESMTP id 31DFB3A5344 for ; Mon, 22 Nov 2004 20:21:08 +0000 (GMT) Received: from ads2.ucomics.com (unknown [65.166.193.11]) by www.bowmansystems.com (Postfix) with ESMTP id 6B8BFC9C7; Mon, 22 Nov 2004 14:10:07 -0600 (CST) Subject: Re: [postgres] Re: Recursive SETOF function From: Richard Rowell To: Mike Rylander Cc: pgsql-sql@postgresql.org In-Reply-To: References: <1101136693.18148.66.camel@richard> <1101149501.18148.81.camel@richard> Content-Type: text/plain Date: Mon, 22 Nov 2004 14:21:00 -0600 Message-Id: <1101154860.18145.93.camel@richard> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200411/237 X-Sequence-Number: 19556 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 > 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 > > > > > > --