SF.net SVN: ledger-smb: [1517] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 1517
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1517&view=rev
Author:   einhverfr
Date:     2007-09-05 21:12:34 -0700 (Wed, 05 Sep 2007)

Log Message:
-----------
Final menu change:  Stored procedure for inserting menu items.

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-09-06 01:02:33 UTC (rev 1516)
+++ trunk/sql/Pg-database.sql	2007-09-06 04:12:34 UTC (rev 1517)
@@ -2572,10 +2572,39 @@
 $$ 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));
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.