http://www.rightbrainnetworks.com/blog/base36-conversion-in-postgresql/

CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
  RETURNS varchar AS $$
        DECLARE
			chars char[];
			ret varchar;
			val bigint;
		BEGIN
		chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
			,'A','B','C','D','E','F','G','H','I','J','K','L','M'
			,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
		val := digits;
		ret := '';
		IF val < 0 THEN
			val := val * -1;
		END IF;
		WHILE val != 0 LOOP
			ret := chars[(val % 36)+1] || ret;
			val := val / 36;
		END LOOP;

		IF min_width > 0 AND char_length(ret) < min_width THEN
			ret := lpad(ret, min_width, '0');
		END IF;

		RETURN ret;

END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- -------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
  RETURNS bigint AS $$
        DECLARE
			a char[];
			ret bigint;
			i int;
			val int;
			chars varchar;
		BEGIN
		chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

		FOR i IN REVERSE char_length(base36)..1 LOOP
			a := a || substring(upper(base36) FROM i FOR 1)::char;
		END LOOP;
		i := 0;
		ret := 0;
		WHILE i < (array_length(a,1)) LOOP		
			val := position(a[i+1] IN chars)-1;
			ret := ret + (val * (36 ^ i));
			i := i + 1;
		END LOOP;

		RETURN ret;

END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

Moje prvni pokusy s ulozenim a traverzovanim grafu v PgSQL:

Volne inspirovano http://www.postgresql.org/docs/8.4/static/queries-with.html


-- tabulka
CREATE TABLE "graph" (
  "id" serial NOT NULL,
  "link" bigint NOT NULL,
  "data" text NULL
)

-- index
-- CREATE INDEX "graph_4d043073083b7" ON "graph" ("id") -- jeste prozkoumat

-- insert (jednoduchy cyklicky graf)
INSERT INTO "graph" ("id", "link", "data") VALUES
('1', '2', 'test a'),
('2', '4', 'test b'),
('3', '1', 'test c'),
('4', '3', 'test d'),
('7', '1', 'oh hai'),
('1', '7', NULL)

-- ------------------------------------------------------------------

-- Tady je rekurzivni query lehce upravena pro potreby kyberky:
-- (pokud se zaindexuje i "link", muzeme snadnou upravou traverzovat i v protismeru: od childa k rodicovi)

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
		SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false
		FROM graph g
		WHERE id = 1  	-- tohle je ID nody ze ktery zaciname
	UNION ALL
		SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path)
		FROM graph g, search_graph sg
		WHERE g.id = sg.link AND NOT cycle
)

SELECT * FROM search_graph
	WHERE cycle != TRUE 	-- vynechame nodu, ktera zjistila ze cyklime (zbytecne by tam byla znovu)
	AND depth <= 3 		-- hloubku omezime na 3 hrany od startu
	LIMIT 100 		-- celkovy pocet vybranych nod omezime na 100