Thread: Recursive SETOF function
I'm trying to port some TSQL to PLPGSQL. The DB has a table with a recursive foreign key that represents a tree hierarchy. I'm trying to re-create a TSQL function that pulls out all the ancestors of a given node in the hierarchy. I'm rather new to PLSQL and I have several questions. 1. In TSQL, I can assign a scalar to the result of query like so: SET @var1 = (SELECT foo FROM bar WHERE bar.uid=@var2) How would I do this in PLSQL? 2. In TSQL the "result table" can be inserted into manually. IE: CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN INSERT @ttable VALUES (1)RETURN END Is there a way to manually insert rows into the result table in PLSQL? What follows is my TSQL function if that helps give context. CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) RETURNS @provider_ids TABLE ( uid INTEGER ) AS BEGIN DECLARE @cid AS INTEGER IF (SELECT count(*) FROM providers WHERE uid =@child_provider) > 0 BEGINSET @cid = @child_providerWHILE@cid IS NOT NULLBEGIN INSERT @provider_ids VALUES (@cid) SET @cid = (SELECT parent_id FROM providersWHERE uid=@cid)END END RETURN END --
I'm feeling sausey today, so here is my (untested) attempt to translate your function. It's inline below, and you'll want to look here http://www.postgresql.org/docs/7.4/interactive/plpgsql.html for more information. On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell <richard@bowmansystems.com> wrote: > I'm trying to port some TSQL to PLPGSQL. The DB has a table with a > recursive foreign key that represents a tree hierarchy. I'm trying to > re-create a TSQL function that pulls out all the ancestors of a given > node in the hierarchy. > > I'm rather new to PLSQL and I have several questions. > > 1. In TSQL, I can assign a scalar to the result of query like so: > SET @var1 = (SELECT foo FROM bar WHERE bar.uid=@var2) > > How would I do this in PLSQL? > > 2. In TSQL the "result table" can be inserted into manually. IE: > > CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN > INSERT @ttable VALUES (1) > RETURN > END > > Is there a way to manually insert rows into the result table in PLSQL? > > What follows is my TSQL function if that helps give context. > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > RETURNS @provider_ids TABLE ( uid INTEGER ) > AS > BEGIN > DECLARE @cid AS INTEGER > IF (SELECT count(*) FROM providers WHERE uid =@child_provider) > 0 > BEGIN > SET @cid = @child_provider > WHILE @cid IS NOT NULL > BEGIN > INSERT @provider_ids VALUES (@cid) > SET @cid = (SELECT parent_id FROM providers WHERE uid=@cid) > END > END > RETURN > END > -- This TYPE will get you a named column... easier to use SRFs with a preexisting type. CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); CREATE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid INTEGER; BEGIN SELECT count(*) FROM providers WHERE uid =@child_provider) > 0 LOOP cid := child_provider IF cid ISNULL THEN EXIT; END IF; RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE uid=@cid; END LOOP; RETURN END;' LANGUAGE 'plpgsql'; Hope that helps! > -- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer
Forgot one line. See below On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <mrylander@gmail.com> wrote: > I'm feeling sausey today, so here is my (untested) attempt to [snip] > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > > RETURNS @provider_ids TABLE ( uid INTEGER ) > > AS > > BEGIN > > DECLARE @cid AS INTEGER > > IF (SELECT count(*) FROM providers WHERE uid =@child_provider) > 0 > > BEGIN > > SET @cid = @child_provider > > WHILE @cid IS NOT NULL > > BEGIN > > INSERT @provider_ids VALUES (@cid) > > SET @cid = (SELECT parent_id FROM providers WHERE uid=@cid) > > END > > END > > RETURN > > END > > > > -- This TYPE will get you a named column... easier to use SRFs with a > preexisting type. > CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); > > CREATE FUNCTION svp_getparentproviderids (INTEGER) > RETURNS SETOF svp_getparentproviderids_uid_type > AS ' > DECLARE > child_provider ALIAS FOR $1; > cid INTEGER; > BEGIN SELECT * FROM providers WHERE uid =@child_provider) > 0 IF NOT FOUND RETURN; END IF; > LOOP > cid := child_provider > IF cid IS NULL THEN > EXIT; > END IF; > RETURN NEXT cid; > SELECT INTO cid parent_id FROM providers WHERE uid=@cid; > END LOOP; > RETURN > END;' LANGUAGE 'plpgsql'; > > Hope that helps! > > > -- > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > -- > Mike Rylander > mrylander@gmail.com > GPLS -- PINES Development > Database Developer > -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer
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
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
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
> >
> >
>
>
--
> SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; Hey, hey.Better : SELECT blablah FROM providers WHERE uid = child_provider LIMIT 1;IF NOT FOUND THEN exit with error ELSE do your stuff Why scan more than 1 row when you just need existence ?Or : SELECT INTO cid parent_id FROM providers WHERE uid=cid;WHILE FOUND RETURN NEXT cid; SELECT INTO cid parent_id FROMproviders WHERE uid=cid;END; Not sure about the While syntax but you get the idea.
Dear friends I hope some one can help me Server machine celeron 2.4Gb with 512 Ram postgres 7.4.5 conectiva 8 with kernel 2.4.19 i´m getting many message ofErro:canceling query due to user request how to solve this i even get with an just booted up server, and running vacuum analyze verbose inside psql with no other people connected the only changes i has on postgresql.conf is max_connection=512 shared_buffers=8192 sort_mem=8192 vacuum_mem=31792 Regards Luiz Rafael
On Wed, Feb 02, 2005 at 11:33:03PM -0200, Luiz Rafael Culik Guimaraes wrote: > i´m getting many message of > Erro:canceling query due to user request What does the following query show? SHOW statement_timeout; If statement_timeout isn't 0 (zero) and if you didn't change it in postgresql.conf, then maybe you're picking it up from a per-user or per-database configuration. You can check such settings with the following queries: SELECT usename, useconfig FROM pg_user; SELECT datname, datconfig FROM pg_database; You can use ALTER USER or ALTER DATABASE to change the settings. -- Michael Fuhr http://www.fuhr.org/~mfuhr/