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
''InexistentID --> %'', 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