- begin;
- CREATE OR REPLACE FUNCTION person_save
- (in_entity_id integer, in_salutation_id int,
- in_first_name text, in_middle_name text, in_last_name text
- )
- RETURNS INT AS $$
- DECLARE
- e_id int;
- e entity;
- loc location;
- l_id int;
- p_id int;
- BEGIN
-
- select * into e from entity where id = in_entity_id and entity_class = 3;
- e_id := in_entity_id;
-
- IF NOT FOUND THEN
- INSERT INTO entity (name, entity_class)
- values (in_first_name || ' ' || in_last_name, 3);
- e_id := currval('entity_id_seq');
-
- END IF;
-
-
- UPDATE person SET
- salutation_id = in_salutation_id,
- first_name = in_first_name,
- last_name = in_last_name,
- middle_name = in_middle_name
- WHERE
- entity_id = in_entity_id;
- IF FOUND THEN
- RETURN in_entity_id;
- ELSE
- -- Do an insert
-
- INSERT INTO person (salutation_id, first_name, last_name, entity_id)
- VALUES (in_salutation_id, in_first_name, in_last_name, e_id);
- RETURN e_id;
-
- END IF;
- END;
- $$ language plpgsql;
- CREATE OR REPLACE FUNCTION employee__save
- (in_entity_id int, in_start_date date, in_end_date date, in_dob date,
- in_role text, in_ssn text, in_sales bool, in_manager_id int, in_employee_number text)
- RETURNS int AS $$
- DECLARE out_id INT;
- BEGIN
- UPDATE entity_employee
- SET startdate = in_start_date,
- enddate = in_end_date,
- dob = in_dob,
- role = in_role,
- ssn = in_ssn,
- manager_id = in_manager_id,
- employeenumber = in_employee_number,
- person_id = (select id FROM person
- WHERE entity_id = in_entity_id)
- WHERE entity_id = in_entity_id;
- out_id = in_entity_id;
- IF NOT FOUND THEN
- INSERT INTO entity_employee
- (startdate, enddate, dob, role, ssn, manager_id,
- employeenumber, entity_id, person_id)
- VALUES
- (in_start_date, in_end_date, in_dob, in_role, in_ssn,
- in_manager_id, in_employee_number, in_entity_id,
- (SELECT id FROM person
- WHERE entity_id = in_entity_id));
- RETURN in_entity_id;
- END IF;
- END;
- $$ LANGUAGE PLPGSQL;
- commit;
|