CREATE OR REPLACE FUNCTION line_of_command( IN the_id int ) RETURNS SETOF int AS $$
DECLARE
the_parent_id int := the_id;
level int := 0;
BEGIN
WHILE the_parent_id <> 0 AND level < 300 LOOP -- beware of loop in data references
RETURN NEXT the_parent_id;
SELECT Parent_id INTO the_parent_id FROM your_table_name_goes_here WHERE Object_id = the_parent_id;
level := level + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql volatile strict;
#> select * from line_of_command( 355 );
postgresq 8.4 has new WITH .. SELECT syntax and windowing functions that could be used
Main Topics
Browse All Topics





by: ivanovnPosted on 2009-03-26 at 07:32:16ID: 23990801
You could probably write a recursive function that would obtain your values. Here is a quick untested example that you can use to get started. Notice that this recursive function inserts the results into a result table. You could probably get it to return results in a more friendly fashion.
Select allOpen in new window