ossp-pkg/as/as-db/bres/bres-name2oid.sql
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;
