Thread: 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
NUMBERCHILD_NODE_ID      NOT NULL NUMBERORDINAL             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

[sNip]
> 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.
       I have one question because I'm not clear about something with your 
implementation (a good one too by the looks of it -- thanks for sharing 
this information); if I start my query from an item at level 5, will the 
level be reflected as such, or will it dynamically start at 1?
       As I understand it, in Oracle the level would begin at 1 in this case.
       Thanks in advance.

-- 
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

>         I have one question because I'm not clear about
> something with your
> implementation (a good one too by the looks of it -- thanks
> for sharing
> this information); if I start my query from an item at level
> 5, will the
> level be reflected as such, or will it dynamically start at 1?
>
>         As I understand it, in Oracle the level would begin at
> 1 in this case.
>

It's the same in this case. The tree building stuff only ever looks down
so the level returned in the query results will start at 1 no matter
where you enter your tree.  In our case we could enter the tree at
'node' 100 and get the tree below that but the function will start at 1
because we only interested in the data below not the entry point and not
where in the tree we entered.

As in the solution if you really want to start it at 5 then set that as
the value of your second parameter.

Cheers,Graeme

[sNip]
> It's the same in this case. The tree building stuff only ever looks down
> so the level returned in the query results will start at 1 no matter
> where you enter your tree.  In our case we could enter the tree at
> 'node' 100 and get the tree below that but the function will start at 1
> because we only interested in the data below not the entry point and not
> where in the tree we entered.
       Thanks.  That's excellent news!  =)

> As in the solution if you really want to start it at 5 then set that as
> the value of your second parameter.
       Interesting.  Although I don't have an immediate need for this, I'm 
sure it could be useful when storing the results of multiple queries of this 
nature in a temporary table.

-- 
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.