--
-- site_node__url/1
--
create or replace function site_node__url(
  url__node_id integer
) returns varchar as $$


    WITH RECURSIVE site_nodes_path(parent_id, path, directory_p, node_id) as (

	select parent_id, ARRAY[name || case when directory_p then '/' else ' ' end]::text[] as path, directory_p, node_id
	from site_nodes where node_id = url__node_id

	UNION ALL

	select sn.parent_id, sn.name::text || snr.path , sn.directory_p, snr.parent_id
	from site_nodes sn join site_nodes_path snr on sn.node_id = snr.parent_id
	where snr.parent_id is not null

    ) select array_to_string(path,'/') from site_nodes_path where parent_id is null

$$ language plpgsql;