I have created a function in pl/pgsql to modify a row before it gets put
into the database, but it seems my modification is being ignored, and
the unmodified row is being inserted.
I have confirmed with this RAISE EXCEPTION that my "NEW" row is modified
properly, however it is not being stored in the db.
NEW.start_date := NEW.start_date+delta;
-- RAISE EXCEPTION ''new start date: % '',NEW.start_date;
NEW.end_date := NEW.end_date+delta;
It's probably something very obvious, but I'm mystified.
Tim
--
-- Function to enforce dependencies in the table structure
--
CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
DECLARE dependent RECORD; delta INTEGER;
BEGIN -- -- First make sure we start on or after end_date of tasks -- that we depend on
-- FOR dependent IN SELECT * FROM project_depend_vw WHERE
project_task_id=NEW.project_task_id LOOP -- -- See if the task we are dependent on
-- ends after we are supposed to start -- IF dependent.end_date >
NEW.start_dateTHEN delta := dependent.end_date-NEW.start_date; -- RAISE
EXCEPTION''delta: % '',delta; NEW.start_date := NEW.start_date+delta; --
RAISEEXCEPTION ''new start date: %
'',NEW.start_date; NEW.end_date := NEW.end_date+delta; END IF;
END LOOP; RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task FOR EACH ROW EXECUTE PROCEDURE
projtask_insert_depend();
