from : http://postgis.refractions.net/pipermail/postgis-users/2007-April/015161.html

-- Shortest path algorithm for PostgreSQL
--
-- Copyright (c) 2005 Sylvain Pasche
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
USA.
--


CREATE TYPE path_result AS (step integer, vertex_id integer, edge_id
integer, cost float8);

-----------------------------------------------------------------------
-- Core function for shortest_path computation
-- See README for description
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION shortest_path(sql text, source_id integer,
target_id integer, 
                                         directed boolean, has_reverse_cost
boolean)
        RETURNS SETOF path_result
        AS '$libdir/libdijkstra.dll'
        LANGUAGE 'C' IMMUTABLE STRICT;


-----------------------------------------------------------------------
-- Drops the vertices and edges tables related to the given geom_table
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION drop_graph_tables(geom_table varchar) RETURNS
void AS
$$
DECLARE 
        vertices_table varchar := quote_ident(geom_table) || '_vertices';
        edges_table varchar := quote_ident(geom_table) || '_edges';
BEGIN

        BEGIN
                EXECUTE 'DROP TABLE ' || vertices_table;
        EXCEPTION 
                WHEN UNDEFINED_TABLE THEN
        END;
        BEGIN
                EXECUTE 'DROP TABLE ' || edges_table;
        EXCEPTION 
                WHEN UNDEFINED_TABLE THEN
        END;
        RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT; 

-----------------------------------------------------------------------
-- This function should not be used directly. Use create_graph_tables
instead
--
-- Insert a vertex into the vertices table if not already there, and
--  return the id of the newly inserted or already existing element
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION insert_vertex(vertices_table varchar, geom_id
anyelement) RETURNS int AS
$$
DECLARE
        vertex_id int;
        myrec record;
BEGIN
        LOOP
                FOR myrec IN EXECUTE 'SELECT id FROM ' ||
quote_ident(vertices_table) || 
                                     ' WHERE geom_id = ' ||
quote_literal(geom_id)  LOOP
                        IF myrec.id IS NOT NULL THEN
                                RETURN myrec.id;
                        END IF;
                END LOOP; 
                EXECUTE 'INSERT INTO ' || quote_ident(vertices_table) || '
(geom_id) VALUES (' || quote_literal(geom_id) || ')';
        END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT; 

-----------------------------------------------------------------------
-- Create the vertices and edges tables from a table matching the 
--  geometry schema described above.
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION create_graph_tables(geom_table varchar,
column_type varchar)
        RETURNS void AS
$$
DECLARE
        geom record;
        edge_id int;
        myrec record;
        source_id int;
        target_id int;
        vertices_table varchar := quote_ident(geom_table) || '_vertices';
        edges_table varchar := quote_ident(geom_table) || '_edges';
BEGIN

        EXECUTE 'CREATE TABLE ' || vertices_table || ' (id serial, geom_id '

                                || quote_ident(column_type) || '  NOT NULL
UNIQUE)';
        EXECUTE 'CREATE INDEX ' || vertices_table || '_id_idx on ' ||
vertices_table || ' (id)';

        EXECUTE 'CREATE TABLE ' || edges_table || ' (id serial, source int,
target int, ' 
                                || 'cost float8, reverse_cost float8, UNIQUE
(source, target))';
        EXECUTE 'CREATE INDEX ' || edges_table || '_source_target_idx on '
|| edges_table || ' (source, target)';

        FOR geom IN EXECUTE 'SELECT gid as id, ' || ' source_id AS source, '
|| 
                                    ' target_id AS target FROM ' ||
quote_ident(geom_table) LOOP

                SELECT INTO source_id insert_vertex(vertices_table,
geom.source);
                SELECT INTO target_id insert_vertex(vertices_table,
geom.target);


                edge_id := nextval(edges_table || '_id_seq');
                EXECUTE 'INSERT INTO ' || edges_table || ' (id, source,
target) VALUES (' 
                     || edge_id || ', '
                     || quote_literal(source_id) || ', '
                     || quote_literal(target_id) || ')';
                
                EXECUTE 'UPDATE ' || quote_ident(geom_table) || ' SET
edge_id = '
                                  || edge_id || ' WHERE gid = ' ||
quote_literal(geom.id);

        END LOOP;
        RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;