DROP    SEQUENCE    menu_data_id_seq;
DROP    TABLE       menu_data;
CREATE  TABLE       menu_data (
    id      SERIAL PRIMARY KEY,
    lft     INTEGER NOT NULL CHECK( lft > 0 ),
    rgt     INTEGER NOT NULL CHECK( rgt > 0 ),
    role_ref    INTEGER DEFAULT 3,
    title       VARCHAR(50) NOT NULL UNIQUE,
    link        TEXT NOT NULL DEFAULT '',
    target      VARCHAR(20) NOT NULL DEFAULT 'Content',
    description TEXT NOT NULL DEFAULT '',
    CHECK( lft < rgt )
);

DROP    VIEW        menu;
CREATE  VIEW menu AS
    SELECT m.*,(( m.rgt - m.lft - 1 ) / 2 ) AS subs,l.level
    FROM   menu_data AS m
           JOIN (
            SELECT      c.title,COUNT(*) AS level
            FROM        menu_data AS p,menu_data AS c
            WHERE       c.lft BETWEEN p.lft AND p.rgt
            GROUP BY    c.title
            ) AS l
           ON m.title = l.title;

DROP    VIEW        menu_parent;
CREATE  VIEW menu_parent AS
    SELECT c.*,p.title AS parent
    FROM   menu AS p,menu AS c
    WHERE  c.lft BETWEEN p.lft AND p.rgt
           AND
           c.rgt BETWEEN p.lft AND p.rgt
           AND
           p.level = c.level - 1;

DROP FUNCTION insertMenu( VARCHAR(20),VARCHAR(20) );
CREATE FUNCTION insertMenu( VARCHAR(20),VARCHAR(20) ) RETURNS INTEGER AS '
DECLARE
    var_title ALIAS FOR $1;
    var_parent ALIAS FOR $2;
    ins_lft INTEGER;
BEGIN
    SELECT (lft + 1) INTO ins_lft FROM menu_data WHERE title = var_parent;
    UPDATE menu_data SET
           lft = CASE WHEN lft >= ins_lft THEN
             lft + 2 ELSE
             lft END,
           rgt = CASE WHEN rgt >= ins_lft THEN
             rgt + 2 ELSE
             rgt END;
    INSERT INTO menu_data ( title,lft,rgt ) VALUES( var_title,ins_lft,ins_lft + 1 );
    RETURN (SELECT last_value FROM menu_data_id_seq);
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION insertMenu( VARCHAR(20),VARCHAR(20),TEXT );
CREATE FUNCTION insertMenu( VARCHAR(20),VARCHAR(20),TEXT ) RETURNS INTEGER AS '
DECLARE
    var_title ALIAS FOR $1;
    var_parent ALIAS FOR $2;
    var_link ALIAS FOR $3;
    ins_lft INTEGER;
BEGIN
    SELECT (lft + 1) INTO ins_lft FROM menu_data WHERE title = var_parent;
    UPDATE menu_data SET
           lft = CASE WHEN lft >= ins_lft THEN
             lft + 2 ELSE
             lft END,
           rgt = CASE WHEN rgt >= ins_lft THEN
             rgt + 2 ELSE
             rgt END;
    INSERT INTO menu_data ( title,link,lft,rgt ) VALUES( var_title,var_link,ins_lft,ins_lft + 1 );
    RETURN (SELECT last_value FROM menu_data_id_seq);
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION deleteMenu( VARCHAR(20) );
CREATE FUNCTION deleteMenu( VARCHAR(20) ) RETURNS INTEGER AS '
DECLARE
    var_title ALIAS FOR $1;
    del_lft INTEGER;
    del_rgt INTEGER; 
    del_id INTEGER;
BEGIN
    SELECT id,lft,rgt INTO del_id,del_lft,del_rgt FROM menu_data WHERE title = var_title;
    DELETE FROM menu_data WHERE lft BETWEEN del_lft AND del_rgt;
    UPDATE menu_data SET
           lft = CASE WHEN lft > del_lft THEN
             lft - ( del_rgt - del_lft + 1 ) ELSE
             lft END,
           rgt = CASE WHEN rgt > del_lft THEN
             rgt - ( del_rgt - del_lft + 1 ) ELSE
             rgt END;
    RETURN del_id;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION upMenu( VARCHAR(20) );
CREATE FUNCTION upMenu( VARCHAR(20) ) RETURNS INTEGER AS '
DECLARE
    var_title ALIAS FOR $1;
    par_lft INTEGER;
    par_rgt INTEGER;
    mv_lft INTEGER;
    mv_rgt INTEGER; 
    mv_id INTEGER;
BEGIN
    SELECT id,lft,rgt INTO mv_id,mv_lft,mv_rgt FROM menu_data WHERE title = var_title;
    SELECT lft,rgt INTO par_lft,par_rgt FROM menu_data WHERE title = ( SELECT parent FROM menu_parent WHERE title = var_title );
    UPDATE menu_data SET
           lft = CASE WHEN lft > par_lft AND lft < mv_lft THEN
             lft + ( mv_rgt - mv_lft + 1 ) ELSE
             CASE WHEN lft >= mv_lft AND lft <= mv_rgt THEN
             lft - ( mv_lft - par_lft - 1 ) ELSE 
             lft END
             END,
               rgt = CASE WHEN rgt > par_lft AND rgt < mv_lft THEN
             rgt + ( mv_rgt - mv_lft + 1 ) ELSE
             CASE WHEN rgt >= mv_lft AND rgt <= mv_rgt THEN
             rgt - ( mv_lft - par_lft - 1 ) ELSE
             rgt END
                 END;
    RETURN mv_id;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION downMenu( VARCHAR(20) );
CREATE FUNCTION downMenu( VARCHAR(20) ) RETURNS INTEGER AS '
DECLARE
    var_title ALIAS FOR $1;
    par_lft INTEGER;
    par_rgt INTEGER;
    mv_lft INTEGER;
    mv_rgt INTEGER;
    mv_id INTEGER;
BEGIN
    SELECT id,lft,rgt INTO mv_id,mv_lft,mv_rgt FROM menu_data WHERE title = var_title;
    SELECT lft,rgt INTO par_lft,par_rgt FROM menu_data WHERE title = ( SELECT parent FROM menu_parent WHERE title = var_title );
    UPDATE menu_data SET
           lft = CASE WHEN lft > mv_rgt AND lft < par_rgt THEN
             lft - ( mv_rgt - mv_lft + 1 ) ELSE
             CASE WHEN lft >= mv_lft AND lft <= mv_rgt THEN
             lft + ( par_rgt - mv_rgt - 1 ) ELSE 
             lft END
                 END,
               rgt = CASE WHEN rgt > mv_rgt AND rgt < par_rgt THEN
             rgt - ( mv_rgt - mv_lft + 1 ) ELSE
             CASE WHEN rgt >= mv_lft AND rgt <= mv_rgt THEN
             rgt + ( par_rgt - mv_rgt - 1 ) ELSE
             rgt END
                 END;
    RETURN mv_id;
END;
' LANGUAGE 'plpgsql';