From: Richard Rowell <richard@bowmansystems.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Breadth first traversal in PLSQL (How to implement Queue?)
Date: Wed, 15 Dec 2004 12:54:44 -0600
Message-ID: <1103136884.9908.22.camel@richard> (raw)
I have a table with a unary (recursive) relationship that represents a
hierarchy. With the gracious help of Mike Rylander I was able to port a
TSQL function that would traverse "up" the hierarchy to PL/SQL. Now I
need help porting the "down" the hierarchy function.
As implemented in TSQL I utilized a simple breadth first tree traversal.
I'm not sure how to replicate this in PL/SQL as I haven't figured out
how to implement the queue required for the breadth first algorithm. My
queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO"
this variable. However when I try to delete the "current" value, I get
a syntax error. If I comment the delete out, I also get an error when I
try to fetch the "next" value from the front of the queue.
Below is the function, followed by the psql output:
CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER)
RETURNS SETOF INTEGER
AS '
DECLARE
parent_provider ALIAS FOR $1;
cid INTEGER;
queue SETOF INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider;
IF cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider;
RETURN;
END IF;
cid := parent_provider;
LOOP
EXIT WHEN cid IS NULL;
RETURN NEXT cid;
SELECT INTO queue uid FROM providers WHERE parent_id = cid;
DELETE FROM queue WHERE queue.queue = cid;
SELECT INTO cid * FROM queue LIMIT 1;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
sp_demo_505=# select * from svp_getchildproviderids(1);
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "svp_getchildproviderids" line 16 at SQL
statement
--
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: Breadth first traversal in PLSQL (How to implement Queue?)
In-Reply-To: <1103136884.9908.22.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