--
-- Selected TOC Entries:
--
\connect - pgsql

--
-- TOC Entry ID 3 (OID 15830772)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: pgsql
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';

--
-- TOC Entry ID 4 (OID 15830773)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';

\connect - dan

--
-- TOC Entry ID 6 (OID 15830774)
--
-- Name: "tree_pathname_set" () Type: FUNCTION Owner: dan
--

CREATE FUNCTION "tree_pathname_set" () RETURNS opaque AS '

DECLARE
       parent_pathname   text;
BEGIN
        RAISE NOTICE ''into tree_pathname_set with %:%:%'', new.id, new.name, new.pathname;
        select pathname 
        into parent_pathname 
        from tree
        where id = new.parent_id;
        if found then
           new.pathname = parent_pathname || ''/'' || new.name;
        else
           new.pathname = ''/'' || new.name;
        end if;
        RETURN new;
    END;' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 2 (OID 15832154)
--
-- Name: tree Type: TABLE Owner: dan
--

CREATE TABLE "tree" (
	"id" integer NOT NULL,
	"parent_id" integer,
	"name" text NOT NULL,
	"pathname" text NOT NULL,
	Constraint "tree_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 5 (OID 15834571)
--
-- Name: "tree_pathname_set_children" () Type: FUNCTION Owner: dan
--

CREATE FUNCTION "tree_pathname_set_children" () RETURNS opaque AS 'BEGIN
        RAISE NOTICE ''into tree_pathname_set_children with %:%:%'', new.id, new.name, new.pathname;

        update tree set pathname = new.pathname || ''/'' || name where parent_id = new.id;

        RETURN new;
    END;' LANGUAGE 'plpgsql';

--
-- Data for TOC Entry ID 7 (OID 15832154)
--
-- Name: tree Type: TABLE DATA Owner: dan
--


COPY "tree" FROM stdin;
1	\N	dan	/dan
2	1	ports	/dan/ports
3	2	security	/dan/ports/security
\.
--
-- TOC Entry ID 8 (OID 15832163)
--
-- Name: tree_pathname_set Type: TRIGGER Owner: dan
--

CREATE TRIGGER "tree_pathname_set" BEFORE INSERT OR UPDATE ON "tree"  FOR EACH ROW EXECUTE PROCEDURE "tree_pathname_set" ();

--
-- TOC Entry ID 9 (OID 15834573)
--
-- Name: tree_pathname_set_children Type: TRIGGER Owner: dan
--

CREATE TRIGGER "tree_pathname_set_children" AFTER INSERT OR UPDATE ON "tree"  FOR EACH ROW EXECUTE PROCEDURE "tree_pathname_set_children" ();