From: Tim Perdue <tim@perdue.net>
To: pgsql-sql@postgresql.org
Subject: pl/pgsql question
Date: Tue, 17 Dec 2002 22:12:27 -0600
Message-ID: <3DFFF5AB.1080004@perdue.net> (raw)
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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tim@perdue.net
Subject: Re: pl/pgsql question
In-Reply-To: <3DFFF5AB.1080004@perdue.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox