Breadth first traversal in PLSQL (How to implement Queue?) - Mailing list pgsql-sql

From Richard Rowell
Subject Breadth first traversal in PLSQL (How to implement Queue?)
Date
Msg-id 1103136884.9908.22.camel@richard
Whole thread Raw
Responses Re: Breadth first traversal in PLSQL (How to implement Queue?)
List pgsql-sql
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 '
DECLAREparent_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
''InexistentID --> %'', 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
WHEREqueue.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


-- 



pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: [Fwd: Majordomo results: unsubscribe]
Next
From: Mike Rylander
Date:
Subject: Re: Breadth first traversal in PLSQL (How to implement Queue?)