diff options
| -rw-r--r-- | sql/modules/Customer.sql | 13 | ||||
| -rw-r--r-- | sql/modules/Entity.sql | 23 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 32 | ||||
| -rw-r--r-- | sql/modules/Person.sql | 4 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 6 |
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; |