-- Creating Functions\012\012-- Renames the sequence OLD_NAME to NEW_NAME ensuring the NEXTVAL() will return the right value.  \012CREATE FUNCTION RENAME_SEQUENCE(OLD_NAME VARCHAR, NEW_NAME VARCHAR) RETURNS INTEGER AS $$\012DECLARE\012  CURR_SEQ_VAL   INTEGER;\012BEGIN\012  SELECT INTO CURR_SEQ_VAL NEXTVAL(OLD_NAME);\012  EXECUTE 'CREATE SEQUENCE ' || NEW_NAME || ' START WITH ' || CURR_SEQ_VAL;\012  EXECUTE 'DROP SEQUENCE ' || OLD_NAME;\012  RETURN CURR_SEQ_VAL;\012END;\012$$ LANGUAGE 'plpgsql';\012\012\012-- Creating Triggers\012\012CREATE OR REPLACE FUNCTION br001_crtl_plate_chk() RETURNS trigger AS $$\012DECLARE\012   cnt           INTEGER;\012   v_cell_plate  CODE;\012BEGIN\012\012   select code into v_cell_plate from sample_types where id  = NEW.saty_id;\012\012   -- Check if the sample is of type "Cell Plate"\012   if v_cell_plate = 'CELL PLATE' then\012\012      -- Check that a Control Plate exists before registering the Cell Plate\012      select count(*) into cnt from samples s, sample_types st where s.saty_id = st.id and st.code = 'CONTROL LAYOUT';\011 \012      IF cnt = 0 THEN\012\011\011 -- Doens't make sense, so comment it out\012         -- RAISE EXCEPTION 'A Control Plate must exist before you can register Cell Plate: %', NEW.code;\012      END IF;\012\012   end if;\012\012   RETURN NEW;\012\012END;\012$$ LANGUAGE 'plpgsql';\012\012\012CREATE TRIGGER br001_crtl_plate_chk BEFORE INSERT ON samples\012    FOR EACH ROW EXECUTE PROCEDURE br001_crtl_plate_chk();\012