Contact entity stored procs bugs fixed

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

5 files changed, 41 insertions, 37 deletions

diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql
index c0a5d640..89867ded 100644
--- a/sql/modules/Customer.sql
+++ b/sql/modules/Customer.sql
@@ -1,5 +1,6 @@
 BEGIN;
 
+
 CREATE TYPE customer_search_return AS (
         legal_name text,
         id int,
@@ -23,6 +24,10 @@ CREATE TYPE customer_search_return AS (
         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 @@ CREATE OR REPLACE FUNCTION customer_save (
     in_notes text, 
     
     in_name text, in_tax_id TEXT,
-    in_threshold
+    in_threshold NUMERIC
     
 ) returns INT as $$
     
@@ -211,10 +216,10 @@ CREATE OR REPLACE FUNCTION customer_retrieve(INT) returns setof customer as $$
     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;
diff --git a/sql/modules/Entity.sql b/sql/modules/Entity.sql
index 2b5aed58..ba98c62a 100644
--- a/sql/modules/Entity.sql
+++ b/sql/modules/Entity.sql
@@ -13,6 +13,14 @@ CREATE OR REPLACE FUNCTION entity_save(
     
         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 @@ CREATE OR REPLACE FUNCTION entity_save(
                 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;
diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql
index 565b7e1b..a379fefb 100644
--- a/sql/modules/Location.sql
+++ b/sql/modules/Location.sql
@@ -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 @@ BEGIN
 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 @@ CREATE OR REPLACE FUNCTION location_search
 (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 @@ BEGIN
 			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 @@ $$ LANGUAGE plpgsql;
 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;
 
diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql
index 1ca1bb16..0426045d 100644
--- a/sql/modules/Person.sql
+++ b/sql/modules/Person.sql
@@ -46,7 +46,7 @@ RETURNS INT AS $$
                 
     
     END IF;
-
+END;
 $$ language plpgsql;
 
-commit;
\ No newline at end of file
+commit;
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
index aac2f4a2..d075b62e 100644
--- a/sql/modules/Vendor.sql
+++ b/sql/modules/Vendor.sql
@@ -229,7 +229,7 @@ CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist
 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 @@ CREATE OR REPLACE FUNCTION vendor_retrieve(INT) returns setof vendor as $$
     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;