Final menu change: Stored procedure for inserting menu items.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1517 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 32 insertions, 3 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 9b3b622b..39c08f4c 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -2572,9 +2572,38 @@ end;
 $$ language plpgsql;
 
 COMMENT ON FUNCTION menu_children(int) IS $$ This function returns all menu items which are children of in_parent_id (the only input parameter. $$;
---
--- PostgreSQL database dump
---
+
+CREATE OR REPLACE FUNCTION 
+menu_insert(in_parent_id int, in_position int, in_label text)
+returns int
+AS $$
+DECLARE
+	new_id int;
+BEGIN
+	UPDATE menu_node 
+	SET position = position * -1
+	WHERE parent = in_parent_id
+		AND position >= in_position;
+
+	INSERT INTO menu_node (parent, position, label)
+	VALUES (in_parent_id, in_position, in_label);
+
+	SELECT INTO new_id currval('menu_node_id_seq');
+
+	UPDATE menu_node 
+	SET position = (position * -1) + 1
+	WHERE parent = in_parent_id
+		AND position < 0;
+
+	RETURN new_id;
+END;
+$$ language plpgsql;
+
+comment on function menu_insert(int, int, text) is $$
+This function inserts menu items at arbitrary positions.  The arguments are, in
+order:  parent, position, label.  The return value is the id number of the menu
+item created. $$;
+
 
 CREATE VIEW menu_friendly AS
     SELECT t."level", t.path, t.list_order, (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label, n.id, n."position" FROM (connectby('menu_node'::text, 'id'::text, 'parent'::text, 'position'::text, '0'::text, 0, ','::text) t(id integer, parent integer, "level" integer, path text, list_order integer) JOIN menu_node n USING (id));