From 78ff281bb9fb208a207fcac6c5169a2ce5067107 Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Sun, 18 Nov 2007 01:06:11 +0000
Subject: 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
---
sql/modules/Customer.sql | 13 +++++++++----
sql/modules/Entity.sql | 23 +++++++++++------------
sql/modules/Location.sql | 32 ++++++++++++++++----------------
sql/modules/Person.sql | 4 ++--
sql/modules/Vendor.sql | 6 +++---
5 files changed, 41 insertions(+), 37 deletions(-)
(limited to 'sql/modules')
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;
--
cgit v1.2.3