diff options
Diffstat (limited to 'sql')
-rw-r--r-- | sql/upgrade/1.2-1.3.sql | 56 |
1 files changed, 56 insertions, 0 deletions
diff --git a/sql/upgrade/1.2-1.3.sql b/sql/upgrade/1.2-1.3.sql index 563d8b55..77fd43b3 100644 --- a/sql/upgrade/1.2-1.3.sql +++ b/sql/upgrade/1.2-1.3.sql @@ -181,4 +181,60 @@ BEGIN END; $$ language plpgsql; +CREATE OR REPLACE FUNCTION location_get (in_id integer) returns locations AS +$$ +DECLARE + location locations%ROWTYPE; +BEGIN + SELECT * INTO location FROM locations WHERE id = in_id; + RETURN location; +END; +$$ language plpgsql; + +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 +AS +$$ +DECLARE + location locations%ROWTYPE; +BEGIN + FOR location IN + SELECT * FROM locations + WHERE companyname ilike '%' || in_companyname || '%' + AND address1 ilike '%' || in_address1 || '%' + AND address2 ilike '%' || in_address2 || '%' + AND in_city ilike '%' || in_city || '%' + AND in_state ilike '%' || in_state || '%' + AND in_zipcode ilike '%' || in_zipcode || '%' + AND in_country ilike '%' || in_country || '%' + LOOP + RETURN NEXT location; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION location_list_all () RETURNS SETOF locations AS +$$ +DECLARE + location locations%ROWTYPE; +BEGIN + FOR location IN + SELECT * FROM locations + ORDER BY company_name, city, state, country + LOOP + RETURN NEXT location; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION location_delete (in_id integer) RETURNS VOID AS +$$ +BEGIN + DELETE FROM locations WHERE id = in_id; +END; +$$ language plpgsql; + COMMIT; |