DROP TABLE foo;
CREATE TABLE foo (
    id     INTEGER,
    parent INTEGER,
    name   TEXT
);

INSERT INTO foo VALUES (1, 1, '');
INSERT INTO foo VALUES (2, 1, 'a');
INSERT INTO foo VALUES (3, 2, 'b');
INSERT INTO foo VALUES (4, 3, 'c');
INSERT INTO foo VALUES (5, 3, 'C');
INSERT INTO foo VALUES (6, 1, 'x');
INSERT INTO foo VALUES (7, 6, 'y');
INSERT INTO foo VALUES (8, 7, 'z');
INSERT INTO foo VALUES (9, 7, 'Z');

DROP FUNCTION name2oid(TEXT);
CREATE FUNCTION name2oid(TEXT) 
RETURNS INTEGER LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    comp TEXT;
    path TEXT;
    oid INTEGER;
BEGIN
    RAISE NOTICE ''name2oid(%)'', $1;
    path := $1;
    IF substr(path, 1, 1) != ''/'' THEN
        RAISE EXCEPTION ''name2oid(%): path not starting with slash'', $1;
    END IF;
    oid := (SELECT id FROM foo
        WHERE name = '''' AND parent = id);
    IF oid IS NULL THEN
        RAISE EXCEPTION ''name2oid(%): root path component not found'', $1;
    END IF;
    path := substr(path, 2);
    -- RAISE NOTICE ''name2oid: "/" -> %'', oid;
    WHILE path != '''' LOOP
        comp := substring(path from ''^([^/]+)/*'');
        path := substring(path from ''^[^/]+/*(.*)$'');
        oid := (SELECT id FROM foo
            WHERE name = comp AND parent = oid);
        IF oid IS NULL THEN
            RAISE EXCEPTION ''name2oid(%): path component "%" not found'', $1, comp, path;
        END IF;
        -- RAISE NOTICE ''name2oid: "%" -> %'', comp, oid;
    END LOOP;
    RAISE NOTICE ''name2oid(%) = %'', $1, oid;
    RETURN oid;
END;
';

DROP FUNCTION oid2name(TEXT);
CREATE FUNCTION oid2name(TEXT) 
RETURNS TEXT LANGUAGE 'plpgsql' AS '
DECLARE
    oid INTEGER;
    name TEXT;
    obj RECORD;
BEGIN
    RAISE NOTICE ''oid2name(%)'', $1;
    oid := $1;
    name := '''';
    LOOP
        SELECT * INTO obj FROM foo WHERE id = oid;
        IF NOT FOUND THEN
            RAISE EXCEPTION ''oid2name(%): object % not found'', $1, oid;
        END IF;
        -- RAISE NOTICE ''oid2name: % -> "%"'', oid, obj.name;
        IF obj.parent = oid THEN
            EXIT;
        END IF;
        name := ''/'' || obj.name || name;
        oid := obj.parent;
    END LOOP;
    IF name = '''' THEN
        name = ''/'';
    END IF;
    RAISE NOTICE ''oid2name(%) = %'', $1, name;
    RETURN name;
END;
';

SELECT * FROM foo WHERE id = (SELECT name2oid('/'));
SELECT * FROM foo WHERE id = (SELECT name2oid('/a/b/c'));
SELECT * FROM foo WHERE id = (SELECT name2oid('/x/y/z'));

SELECT oid2name(1);
SELECT oid2name(4);
SELECT oid2name(8);
SELECT *,oid2name(id) from foo;

BEGIN TRANSACTION;
SELECT * FROM foo WHERE id = name2oid('/x/y/z');
SELECT * FROM foo WHERE id = name2oid('/x/y/z');
COMMIT;

DROP FUNCTION fuck(TEXT);
CREATE FUNCTION fuck(TEXT) 
RETURNS TEXT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    foo RECORD;
BEGIN
    RAISE NOTICE ''fuck'';
    SELECT * INTO foo FROM foo;
    RETURN foo.name;
END;
';

BEGIN TRANSACTION;
SELECT * FROM foo WHERE fuck('foo') != 'fuck';
SELECT * FROM foo WHERE fuck('foo') != 'fuck';
SELECT * FROM foo WHERE fuck('foo') != 'fuck';
COMMIT;