SF.net SVN: ledger-smb: [1874] trunk/sql/modules

[..hidden..]

Revision: 1874
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1874&view=rev
Author:   einhverfr
Date:     2007-11-17 17:06:11 -0800 (Sat, 17 Nov 2007)

Log Message:
-----------
Contact entity stored procs bugs fixed

Modified Paths:
--------------
    trunk/sql/modules/Customer.sql
    trunk/sql/modules/Entity.sql
    trunk/sql/modules/Location.sql
    trunk/sql/modules/Person.sql
    trunk/sql/modules/Vendor.sql

Modified: trunk/sql/modules/Customer.sql
===================================================================
--- trunk/sql/modules/Customer.sql	2007-11-18 00:36:36 UTC (rev 1873)
+++ trunk/sql/modules/Customer.sql	2007-11-18 01:06:11 UTC (rev 1874)
@@ -1,5 +1,6 @@
 BEGIN;
 
+
 CREATE TYPE customer_search_return AS (
         legal_name text,
         id int,
@@ -23,6 +24,10 @@
         note text
 );
 
+-- COMMENT ON TYPE customer_search_result IS
+-- $$ This structure will change greatly in 1.4.  
+-- If you want to reply on it heavily, be prepared for breakage later.  $$;
+
 CREATE OR REPLACE FUNCTION customer_save (
     in_id int,
     
@@ -37,7 +42,7 @@
     in_notes text, 
     
     in_name text, in_tax_id TEXT,
-    in_threshold
+    in_threshold NUMERIC
     
 ) returns INT as $$
     
@@ -211,10 +216,10 @@
     where v.id = $1;
     
 $$ language 'sql';
-COMMIT;
 
-CREATE OR REPLACE FUNCTION customer_next_customer_id() returns int as $$
+CREATE OR REPLACE FUNCTION customer_next_customer_id() returns bigint as $$
     
     select nextval('company_id_seq');
     
-$$ language 'sql';Æ
+$$ language 'sql';
+COMMIT;

Modified: trunk/sql/modules/Entity.sql
===================================================================
--- trunk/sql/modules/Entity.sql	2007-11-18 00:36:36 UTC (rev 1873)
+++ trunk/sql/modules/Entity.sql	2007-11-18 01:06:11 UTC (rev 1874)
@@ -13,6 +13,14 @@
     
         select * into e from entity where id = in_entity_id;
         
+            
+        update 
+            entity 
+        SET
+            name = in_name,
+            entity_class = in_entity_class
+        WHERE
+            id = in_entity_id;
         IF NOT FOUND THEN
             -- do the insert magic.
             e_id = nextval('entity_id_seq');
@@ -22,20 +30,11 @@
                 in_entity_class
                 );
             return e_id;
-            
-        ELSIF FOUND THEN
-            
-            update 
-                entity 
-            SET
-                name = in_name
-                entity_class = in_entity_class
-            WHERE
-                id = in_entity_id;
-            return in_entity_id;
         END IF;
+        return in_entity_id;
+            
     END;
 
 $$ language 'plpgsql';
 
-commit;
\ No newline at end of file
+commit;

Modified: trunk/sql/modules/Location.sql
===================================================================
--- trunk/sql/modules/Location.sql	2007-11-18 00:36:36 UTC (rev 1873)
+++ trunk/sql/modules/Location.sql	2007-11-18 01:06:11 UTC (rev 1874)
@@ -7,7 +7,7 @@
 DECLARE
 	location_id integer;
 BEGIN
-	UPDATE locations
+	UPDATE location
 	SET companyname = in_companyname,
 		address1 = in_address1,
 		address2 = in_address2,
@@ -29,13 +29,13 @@
 END;
 $$ LANGUAGE PLPGSQL;
 
-CREATE OR REPLACE FUNCTION location_get (in_id integer) returns locations AS
+CREATE OR REPLACE FUNCTION location_get (in_id integer) returns location AS
 $$
 DECLARE
-	location locations%ROWTYPE;
+	out_location location%ROWTYPE;
 BEGIN
-	SELECT * INTO location FROM locations WHERE id = in_id;
-	RETURN location;
+	SELECT * INTO out_location FROM location WHERE id = in_id;
+	RETURN out_location;
 END;
 $$ language plpgsql;
 
@@ -43,14 +43,14 @@
 (in_companyname varchar, in_address1 varchar, in_address2 varchar, 
 	in_city varchar, in_state varchar, in_zipcode varchar, 
 	in_country varchar)
-RETURNS SETOF locations
+RETURNS SETOF location
 AS
 $$
 DECLARE
-	location locations%ROWTYPE;
+	out_location location%ROWTYPE;
 BEGIN
-	FOR location IN
-		SELECT * FROM locations 
+	FOR out_location IN
+		SELECT * FROM location 
 		WHERE companyname ilike '%' || in_companyname || '%'
 			AND address1 ilike '%' || in_address1 || '%'
 			AND address2 ilike '%' || in_address2 || '%'
@@ -59,21 +59,21 @@
 			AND in_zipcode ilike '%' || in_zipcode || '%'
 			AND in_country ilike '%' || in_country || '%'
 	LOOP
-		RETURN NEXT location;
+		RETURN NEXT out_location;
 	END LOOP;
 END;
 $$ LANGUAGE PLPGSQL;
 
-CREATE OR REPLACE FUNCTION location_list_all () RETURNS SETOF locations AS
+CREATE OR REPLACE FUNCTION location_list_all () RETURNS SETOF location AS
 $$
 DECLARE 
-	location locations%ROWTYPE;
+	out_location location%ROWTYPE;
 BEGIN
-	FOR location IN
-		SELECT * FROM locations 
+	FOR out_location IN
+		SELECT * FROM location 
 		ORDER BY company_name, city, state, country
 	LOOP
-		RETURN NEXT location;
+		RETURN NEXT out_location;
 	END LOOP;
 END;
 $$ LANGUAGE plpgsql;
@@ -81,7 +81,7 @@
 CREATE OR REPLACE FUNCTION location_delete (in_id integer) RETURNS VOID AS
 $$
 BEGIN
-	DELETE FROM locations WHERE id = in_id;
+	DELETE FROM location WHERE id = in_id;
 END;
 $$ language plpgsql;
 

Modified: trunk/sql/modules/Person.sql
===================================================================
--- trunk/sql/modules/Person.sql	2007-11-18 00:36:36 UTC (rev 1873)
+++ trunk/sql/modules/Person.sql	2007-11-18 01:06:11 UTC (rev 1874)
@@ -46,7 +46,7 @@
                 
     
     END IF;
-
+END;
 $$ language plpgsql;
 
-commit;
\ No newline at end of file
+commit;

Modified: trunk/sql/modules/Vendor.sql
===================================================================
--- trunk/sql/modules/Vendor.sql	2007-11-18 00:36:36 UTC (rev 1873)
+++ trunk/sql/modules/Vendor.sql	2007-11-18 01:06:11 UTC (rev 1874)
@@ -229,7 +229,7 @@
 CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
 CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
     
-CREATE INDEX location_city_prov_gist_idx ON location USING gist(city_province gist_trgm_ops);
+CREATE INDEX location_city_prov_gist_idx ON location USING gist(city gist_trgm_ops);
 CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
 
 CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, 
@@ -278,10 +278,10 @@
     where v.id = $1;
     
 $$ language 'sql';
-COMMIT;
 
-CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns int as $$
+CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns bigint as $$
     
     select nextval('company_id_seq');
     
 $$ language 'sql';
+COMMIT;


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