Re: [postgres] Re: Recursive SETOF function
От | Richard Rowell |
---|---|
Тема | Re: [postgres] Re: Recursive SETOF function |
Дата | |
Msg-id | 1101154860.18145.93.camel@richard обсуждение исходный текст |
Ответ на | Re: [postgres] Re: Recursive SETOF function (Mike Rylander <mrylander@gmail.com>) |
Список | 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 ' DECLAREchild_provider ALIAS FOR $1;cid INTEGER; BEGIN SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; IF cid = 0 THEN RAISE EXCEPTION ''InexistentID --> %'', 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 providersWHERE 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@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 > > > > > > --
В списке pgsql-sql по дате отправления: