From: Richard Rowell <richard@bowmansystems.com>
To: pgsql-sql@postgresql.org
Subject: Re: Recursive SETOF function
Date: Mon, 22 Nov 2004 13:25:52 -0600
Message-ID: <1101151552.18145.86.camel@richard> (raw)
In-Reply-To: <b918cf3d04112208564c5fb783@mail.gmail.com>
References: <1101136693.18148.66.camel@richard>
<b918cf3d041122085437cde2f1@mail.gmail.com>
<b918cf3d04112208564c5fb783@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@bowmansystems.com
Subject: Re: Recursive SETOF function
In-Reply-To: <1101151552.18145.86.camel@richard>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox