[SQL] Breadth first traversal in PLSQL (How to implement Queue?)

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 uti

Re: [postgres] Re: [SQL] Recursive SETOF function

mp_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 p

Re: [SQL] Recursive SETOF function

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_

[SQL] 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

[SQL] Database Upgrade scripts (AKA Conditional SQL execution)

type ='U' ) < 1 THEN CREATE TABLE foo() END Can I accomplish this with postgresql without involving an external process (like say perl)? I guess I could put the upgrade stuff into PL/SQL functions and just drop the functions when I'm done, but I was hoping for something

[SQL] to_date confusion

I'm confused. How do I get the integer 10102 to come in as the date 2002-01-01? cmi=> select to_date('010102','MMDDYY'); to_date 2002-01-01 (1 row) cmi=> select to_char(10102,'00'); to_char - 010102 (1 row) cmi=> select to_date(to_char(10102,'00'),'MMDDYY'

[SQL] to_date() confusion

lect to_date(to_char(10102,'00'),'MMDDYY'); to_date 2010-01-10 (1 row) TIA! -- Richard Rowell <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an

[SQL] Outer Join Syntax

I'm doing a feasability study on porting our flagship product to Postgres (from MS_SQL). I have run across a few snags, the largest of which is the outer join syntax. MS has some nice syntactical sugar with the *=/=* operators that Postgres dosen't seem to support. I am confused on how to repli

[SQL] Re: Auto increment

In article , "Mads Jensen" <[EMAIL PROTECTED]> wrote: > Hi > > I'm a newbiw with pgsql: > > 1: Haven't been able to find the officiel manual to pgsql. What's the > complete URL? Hmm, I don't know, I use the book which you can find with all the rest of the docs at:

[SQL] Can I get this all in one query?

I'm designing a database/website that will allow students to "grade" the professors/classes the students attend. There are eight different "factors" that the students assign grades on. Until tonight I had one table that kept the scores assigned by students. This table name REVIEW had a field n