SOLVED: Emulating 'connect by prior' using stored proc

First off, props for this go to Chris Wenham from his Live Journal post
http://www.livejournal.com/users/terrulen/6008.html. He's happy for me
to pass on the secrets :)

Standard disclaimer applies but it worked well for me with no mods to
our table structure although I had to adjust the function that contained
the code to allow for the fact that the function returns the actual node
you start with whereas Oracle does not. I cheated and used array_shift()
in PHP. YMMV.

Problem: Hosting company who would never in a million years let you
install tablefunc and you needed to help your application ported from
Oracle working nicely.

Solution: postgreSQL 7.3 now has lovely additions to stored procedures
including returning row sets and recursion.

Our table looked like:
Name Null? Type
------------------- -------- -------
PARENT_NODE_ID NOT NULL NUMBER
CHILD_NODE_ID NOT NULL NUMBER
ORDINAL NUMBER

And the Oracle query was:
select child_node_id, level
from node_relationships
connect by prior child_node_id = parent_node_id
start with parent_node_id=682904
order by Hierarchy.Branch(level, ordinal)

In order to make this work with postgres an additional table is needed
that can hold the level (depth) of the branch because pgsql doesn't like
returning a tuple that isn't based on a defined structure. Once you've
created this table you can pretty much forget about it.

CREATE TABLE "node_relationships_n_level" (
"level" integer
) inherits (node_relationships);

Now create your stored procedure.

CREATE OR REPLACE FUNCTION "crawl_tree" (integer,integer) RETURNS SETOF
node_relationships_n_level AS 'DECLARE
temp RECORD;
child RECORD;
BEGIN
SELECT INTO temp *, $2 AS level FROM node_relationships WHERE
child_node_id = $1;

IF FOUND THEN
RETURN NEXT temp;
FOR child IN SELECT child_node_id FROM node_relationships WHERE
parent_node_id = $1 ORDER BY ordinal LOOP
FOR temp IN SELECT * FROM crawl_tree(child.child_node_id, $2 +
1) LOOP
RETURN NEXT temp;
END LOOP;
END LOOP;
END IF;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

The second parameter must be zero. This is a kludge because this is a
recursive function and I needed some way of passing the level to
successive function calls. However, if you like, you could consider this
to be a "level offset"--set it to '2' and all the levels returned will
be n + 2.

Execute "SELECT * FROM crawl_tree(682904,0)" and you're done.

Hope this helps people.

Cheers,
Graeme