pl/pgsql question
От | Tim Perdue |
---|---|
Тема | pl/pgsql question |
Дата | |
Msg-id | 3DFFF5AB.1080004@perdue.net обсуждение исходный текст |
Ответы |
Re: pl/pgsql question
Re: pl/pgsql question |
Список | pgsql-sql |
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();
В списке pgsql-sql по дате отправления: