diff options
Diffstat (limited to 'sql/modules')
| -rw-r--r-- | sql/modules/Employee.sql | 2 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 65 | ||||
| -rw-r--r-- | sql/modules/Person.sql | 106 | ||||
| -rw-r--r-- | sql/modules/admin.sql | 1 |
4 files changed, 136 insertions, 38 deletions
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql index ae2efc86..0048b873 100644 --- a/sql/modules/Employee.sql +++ b/sql/modules/Employee.sql @@ -23,7 +23,7 @@ returns int AS $$ select * into p from person where id = in_person; IF NOT FOUND THEN - RAISE EXCEPTION 'No person found for ID %', in_person; + RAISE EXCEPTION 'No person found for ID %', in_perso; END IF; -- Okay, we're good. Check to see if we update or insert. diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql index 4e0cb6b1..e266f277 100644 --- a/sql/modules/Location.sql +++ b/sql/modules/Location.sql @@ -27,7 +27,7 @@ END; $$ language plpgsql; CREATE OR REPLACE FUNCTION location_save -(in_address1 text, in_address2 text, in_address3 text, +(in_location_id int, in_address1 text, in_address2 text, in_address3 text, in_city text, in_state text, in_zipcode text, in_country int) returns integer AS $$ @@ -36,29 +36,54 @@ DECLARE location_row RECORD; BEGIN - SELECT * INTO location_row FROM location - WHERE line_one = in_address1 AND - coalesce(line_two, '') = coalesce(in_address2, '') AND - coalesce(line_three, '') = coalesce(in_address3, '') AND - city = in_city AND - coalesce(state, '') = coalesce(in_state, '') AND - coalesce(mail_code, '') = coalesce(in_zipcode, '') AND - country_id = in_country - LIMIT 1; - IF FOUND THEN - return location_row.id; + IF in_location_id IS NULL THEN + -- Straight insert. + location_id = nextval('location_id_seq'); + INSERT INTO location ( + id, + line_one, + line_two, + line_three, + city, + state, + zipcode, + country) + VALUES ( + location_id, + in_address1, + in_address2, + in_address3, + in_city, + in_state, + in_zipcode, + in_country + ); + return location_id; + ELSE + -- Test it. + SELECT * INTO location_row WHERE id = in_location_id; + IF NOT FOUND THEN + -- Tricky users are lying to us. + RAISE EXCEPTION "location_save called with nonexistant location ID %", in_location_id; + ELSE + -- Okay, we're good. + + UPDATE location SET + line_one = in_address1, + line_two = in_address2, + line_three = in_address3, + city = in_city, + state = in_state, + zipcode = in_zipcode, + country = in_country + WHERE id = in_location_id; + return in_location_id; + END IF; END IF; - INSERT INTO location - (line_one, line_two, line_three, city, state, mail_code, country_id, - created) - VALUES - (in_address1, in_address2, in_address3, in_city, in_state, - in_zipcode, in_country, now()); - SELECT currval('location_id_seq') INTO location_id; - return location_id; END; $$ LANGUAGE PLPGSQL; + COMMENT ON function location_save (in_companyname text, in_address1 text, in_address2 text, in_city text, in_state text, in_zipcode text, in_country int) IS diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql index 9eda8335..f942b137 100644 --- a/sql/modules/Person.sql +++ b/sql/modules/Person.sql @@ -131,36 +131,108 @@ BEGIN END; $$ LANGUAGE PLPGSQL; -create or replace function person_location_save( - in_entity_id int, in_location_id int, - in_line_one text, in_line_two text, - in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text, + +/*( + unknown, + unknown, + unknown, + unknown, + unknown, + unknown, + unknown, + unknown) + +*/ +create or replace function person__save_location( + in_entity_id int, + in_location_id int, + in_line_one text, + in_line_two text, + in_line_three text, + in_city TEXT, + in_state TEXT, + in_mail_code text, in_country_code int ) returns int AS $$ DECLARE l_row location; l_id INT; - t_person_id int; + t_person_id int; BEGIN SELECT id INTO t_person_id FROM person WHERE entity_id = in_entity_id; + -- why does it delete? + + select * into l_row FROM location + WHERE id = in_location_id; + + IF NOT FOUND THEN + -- Create a new one. + l_id := location_save( + in_location_id, + in_line_one, + in_line_two, + in_line_three, + in_city, + in_state, + in_mail_code, + in_country_code); + + INSERT INTO person_to_location + (person_id, location_id) + VALUES (t_person_id, l_id); + ELSE + l_id := location_save( + in_location_id, + in_line_one, + in_line_two, + in_line_three, + in_city, + in_state, + in_mail_code, + in_country_code); + -- Update the old one. + END IF; + return l_id; + END; +$$ language 'plpgsql'; - DELETE FROM person_to_location - WHERE person_id = t_person_id - AND location_id = in_location_id; +CREATE OR REPLACE FUNCTION person__delete_location ( + in_entity_id INT, in_location_id INT +) returns int AS $$ - SELECT location_save(in_line_one, in_line_two, in_line_three, in_city, - in_state, in_mail_code, in_country_code) - INTO l_id; +DECLARE + v_loc location; + +BEGIN + + select loc.* into v_loc FROM location loc + JOIN person_to_location ptl ON loc.id = ptl.location_id + JOIN person p ON p.id = ptl.person_id + WHERE p.entity_id = in_entity_id + AND loc.id = in_location_id; + + IF NOT FOUND THEN + RAISE EXCEPTION "Cannot find records to delete for entity % and location %", in_entity_id, in_location_id; + ELSE + DELETE FROM people_to_location WHERE location_id = in_location_id; + DELETE FROM location WHERE location_id = in_location_id; + END IF; + +END; - INSERT INTO person_to_location - (person_id, location_id) - VALUES (t_person_id, l_id); +$$ language plpgsql; - RETURN l_id; - END; +CREATE OR REPLACE FUNCTION person__all_locations ( + in_entity_id int +) returns setof location AS $$ -$$ language 'plpgsql'; + SELECT l.* FROM location l + JOIN person_to_location ptl ON ptl.location_id = l.id + JOIN person p on ptl.person_id = p.id + WHERE p.id = $1; + +$$ language sql; commit; diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql index f590aa94..1055ff29 100644 --- a/sql/modules/admin.sql +++ b/sql/modules/admin.sql @@ -472,6 +472,7 @@ BEGIN pg_roles where rolname ~ ('^lsmb_' || in_database) + order by rolname ASC LOOP RETURN NEXT v_rol; END LOOP; |