Sorry about that... try this:
CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS '
DECLAREchild_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
''InexistentID --> %'', child_provider; RETURN; END IF; cid.uid := child_provider; LOOP EXIT WHEN tmp_cid
ISNULL; 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