Message-ID: <3DFFF5AB.1080004@perdue.net>
Date: Tue, 17 Dec 2002 22:12:27 -0600
From: Tim Perdue <tim@perdue.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3a) Gecko/20021123
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: pl/pgsql question
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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_date THEN
                         delta := dependent.end_date-NEW.start_date;
                 --      RAISE EXCEPTION ''delta: % '',delta;
                         NEW.start_date := NEW.start_date+delta;
                 --      RAISE EXCEPTION ''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();