CREATE OR REPLACE FUNCTION "public"."traverse_tree" (tree_id uuid) RETURNS SETOF "public"."node" AS
$body$
my $uuid=shift @_;

# Build array of root nodes
my @rootnodes;
if ( defined($uuid) ) {
	push @rootnodes, { node_id => $uuid, node_level => 0 };
}
else {
	my $root_sth=spi_query(<<"EOM");
SELECT tree.id AS node_id
FROM tree 
WHERE tree.parent_id IS NULL
ORDER BY LOWER(name) ASC
EOM
    while( defined( my $row=spi_fetchrow($root_sth) ) ) {
    	push @rootnodes, { node_id => $row->{'node_id'}, node_level => 0 };
    }
}

# Depth-first search over neighbours
my %visited;
my @stack=reverse @rootnodes;
my $sort_number=0;
while( @stack > 0 ) {

	# Remove one element from stack
	my $current=pop @stack;
    my $current_id=$current->{'node_id'};
    my $level=$current->{'node_level'};

	# Return it
    return_next({
    	'node_id'          => $current_id,
        'node_type'        => 'tree',
        'node_number'      => $sort_number,
        'node_level'       => $level,
    });

	# Set it visited and increase counter
    $visited{$current_id}=1;
    $sort_number++;

	# Fetch all neighbours, and put into stack the ones
    # that have not been visited, reverse input ordering
    # is important to maintain our sort order
    my @temp_stack;
	my $neighbour_sth=spi_query("SELECT id AS node_id FROM tree WHERE parent_id='$current_id'");
    while( defined( my $row = spi_fetchrow($neighbour_sth) ) ) {
		unless ( $visited{ $row->{'node_id'} } ) {
    		push @temp_stack, { node_id => $row->{'node_id'}, node_level => ($level+1) };
        }
    }
    push @stack, reverse @temp_stack;
}

return;
$body$
LANGUAGE 'plperl'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;