Skip site navigation (1) Skip section navigation (2)

Re: Recursive SETOF function

From: Richard Rowell <richard(at)bowmansystems(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Recursive SETOF function
Date: 2004-11-22 19:25:52
Message-ID: 1101151552.18145.86.camel@richard (view raw or flat)
Thread:
Lists: pgsql-sql
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



In response to

Responses

pgsql-sql by date

Next:From: Mike RylanderDate: 2004-11-22 19:39:21
Subject: Re: [postgres] Re: Recursive SETOF function
Previous:From: Mike RylanderDate: 2004-11-22 16:56:31
Subject: Re: Recursive SETOF function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group