summaryrefslogtreecommitdiff
path: root/sql/modules/Location.sql
blob: e266f277be80c13c2cd58737b5c9c111e409cb97 (plain)
  1. -- VERSION 1.3.0
  2. CREATE OR REPLACE FUNCTION location_list_class()
  3. RETURNS SETOF location_class AS
  4. $$
  5. DECLARE out_row RECORD;
  6. BEGIN
  7. FOR out_row IN
  8. SELECT * FROM location_class ORDER BY id
  9. LOOP
  10. RETURN NEXT out_row;
  11. END LOOP;
  12. END;
  13. $$ language plpgsql;
  14. CREATE OR REPLACE FUNCTION location_list_country()
  15. RETURNS SETOF country AS
  16. $$
  17. DECLARE out_row RECORD;
  18. BEGIN
  19. FOR out_row IN
  20. SELECT * FROM country ORDER BY name
  21. LOOP
  22. RETURN NEXT out_row;
  23. END LOOP;
  24. END;
  25. $$ language plpgsql;
  26. CREATE OR REPLACE FUNCTION location_save
  27. (in_location_id int, in_address1 text, in_address2 text, in_address3 text,
  28. in_city text, in_state text, in_zipcode text, in_country int)
  29. returns integer AS
  30. $$
  31. DECLARE
  32. location_id integer;
  33. location_row RECORD;
  34. BEGIN
  35. IF in_location_id IS NULL THEN
  36. -- Straight insert.
  37. location_id = nextval('location_id_seq');
  38. INSERT INTO location (
  39. id,
  40. line_one,
  41. line_two,
  42. line_three,
  43. city,
  44. state,
  45. zipcode,
  46. country)
  47. VALUES (
  48. location_id,
  49. in_address1,
  50. in_address2,
  51. in_address3,
  52. in_city,
  53. in_state,
  54. in_zipcode,
  55. in_country
  56. );
  57. return location_id;
  58. ELSE
  59. -- Test it.
  60. SELECT * INTO location_row WHERE id = in_location_id;
  61. IF NOT FOUND THEN
  62. -- Tricky users are lying to us.
  63. RAISE EXCEPTION "location_save called with nonexistant location ID %", in_location_id;
  64. ELSE
  65. -- Okay, we're good.
  66. UPDATE location SET
  67. line_one = in_address1,
  68. line_two = in_address2,
  69. line_three = in_address3,
  70. city = in_city,
  71. state = in_state,
  72. zipcode = in_zipcode,
  73. country = in_country
  74. WHERE id = in_location_id;
  75. return in_location_id;
  76. END IF;
  77. END IF;
  78. END;
  79. $$ LANGUAGE PLPGSQL;
  80. COMMENT ON function location_save
  81. (in_companyname text, in_address1 text, in_address2 text,
  82. in_city text, in_state text, in_zipcode text, in_country int) IS
  83. $$ Note that this does NOT override the data in the database.
  84. Instead we search for locations matching the desired specifications and if none
  85. are found, we insert one. Either way, the return value of the location can be
  86. used for mapping to other things. This is necessary because locations are
  87. only loosly coupled with entities, etc.$$;
  88. CREATE OR REPLACE FUNCTION location_get (in_id integer) returns location AS
  89. $$
  90. DECLARE
  91. out_location location%ROWTYPE;
  92. BEGIN
  93. SELECT * INTO out_location FROM location WHERE id = in_id;
  94. RETURN out_location;
  95. END;
  96. $$ language plpgsql;
  97. CREATE OR REPLACE FUNCTION location_search
  98. (in_companyname varchar, in_address1 varchar, in_address2 varchar,
  99. in_city varchar, in_state varchar, in_zipcode varchar,
  100. in_country varchar)
  101. RETURNS SETOF location
  102. AS
  103. $$
  104. DECLARE
  105. out_location location%ROWTYPE;
  106. BEGIN
  107. FOR out_location IN
  108. SELECT * FROM location
  109. WHERE companyname ilike '%' || in_companyname || '%'
  110. AND address1 ilike '%' || in_address1 || '%'
  111. AND address2 ilike '%' || in_address2 || '%'
  112. AND in_city ilike '%' || in_city || '%'
  113. AND in_state ilike '%' || in_state || '%'
  114. AND in_zipcode ilike '%' || in_zipcode || '%'
  115. AND in_country ilike '%' || in_country || '%'
  116. LOOP
  117. RETURN NEXT out_location;
  118. END LOOP;
  119. END;
  120. $$ LANGUAGE PLPGSQL;
  121. CREATE OR REPLACE FUNCTION location_list_all () RETURNS SETOF location AS
  122. $$
  123. DECLARE
  124. out_location location%ROWTYPE;
  125. BEGIN
  126. FOR out_location IN
  127. SELECT * FROM location
  128. ORDER BY company_name, city, state, country
  129. LOOP
  130. RETURN NEXT out_location;
  131. END LOOP;
  132. END;
  133. $$ LANGUAGE plpgsql;
  134. CREATE OR REPLACE FUNCTION location_delete (in_id integer) RETURNS VOID AS
  135. $$
  136. BEGIN
  137. DELETE FROM location WHERE id = in_id;
  138. END;
  139. $$ language plpgsql;
  140. CREATE TYPE location_result AS (
  141. id int,
  142. line_one text,
  143. line_two text,
  144. line_three text,
  145. city text,
  146. state text,
  147. mail_code text,
  148. country text,
  149. class text
  150. );